I have a date lets say 11/05/2008 in cell A1. In cell A2 i want it to show a period like 01_08 this being mth 1 of the financial year and 08 being the year. I want June to be shown as 02, July as 03 and so on. Is there a formula for this ? ( I will be importing info from Sage)
auzzie Sun 11/05/08 15:40
To subscribe to this question you need to
sign in to the AnswerBank or register
if you are not already a member. All you need is a valid email address to register.
|
|
I hope I have understood what you want.
You want 18/12/2008 to show as 08_08
but I'm not sure whether you want January to still show 08 or to show the actual year 09
eg
19/01/09 to show as 09_08 or as 09_09?
I am assuming the former (08). If so the formula you need is:
=RIGHT(TEXT(100+1+MOD((MONTH(A1)+7);12);"#");2) & "_" & RIGHT(TEXT(YEAR(A1)-1*(MONTH(A1)<5);"#");2)
Get back to me if you want it to say 09 for the year in January and I will post the modified formula.
|
|
|
Question Author
Gen, thanks for the quick reply but I am getting an "error" with the formula. I copied it manually and copy and pasted it from the website still comes up the same.
Can i also ask, what might be a stupid question. In your answer you have 18/12/08 as 08_08 is this just a scenario? Only how did you arrive at 08 as the number for December?
Many thanks, once again
|
|
|
The error is from semicolons in place of commas.
From your explanation of the series
01... May
02... Jun
03... Jul
etc.
07... Nov
08... Dec
etc.
gen2 - you can remove a bit of redundancy from your formula.
=TEXT(1+MOD((MONTH(A1)+7),12),"00") & "_" & RIGHT(YEAR(A1)-1*(MONTH(A1)<5),2)
|
|
|
Hi again.
Apoligies for the formula giving an error. I don't have Excel on this laptop and checked it in OpenOffice. I think the only difference is that Excel used commas where OpenOffice uses semicolons.
Here it is with commas:
=RIGHT(TEXT(100+1+MOD((MONTH(A1)+7),12),"#"),2) & "_" & RIGHT(TEXT(YEAR(A1)-1*(MONTH(A1)<5),"#"),2)
Now the month numbers:
You said you wanted 11/05/2008 to show as 01_08
so
11/06/2008 shows as 02_08
11/07/2008 shows as 03_08
.
.
.
11/11/2008 shows as 07_08
11/12/2008 shows as 08_08
Then do you want
11/01/09 to show as 09_09 or as 09_08?
|
|
|
Question Author
Sorry Gen i was being a bit thick with the dates!! I will want jan to stay as 08 but to change to 09 when may comes around again, if that makes senses.
Kempie can you shorten that? Thanks for all the info both
|
|
|
That's OK then - my formula does just that.
Also, Kempie's one is the same but slightly shorter.
Try them and check they work.
|
|
Myspace Layouts | Problem Mortgage | Online Advertising | Mortgages | Latin books Logic books Rhetoric texts
