Dates in Excel

[auzzie]

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)

[gen2]

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.

[auzzie]

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

[kempie]

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)

[gen2]

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?

[auzzie]

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

[gen2]

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.

[auzzie]

Thanks both