Formating Fiscal Quarters, rather than Months

P

PatK

I would like to be able to format a date based upon fiscal quarter, rather
than simply Month year. This would be based upon the underlining data: For
example, what I would like to see:

Q1FY06 Q2FY06 Q3FY06 Q4FY06

Would be created by inputing:

11/1/2005 02/01/2005 05/01/2005 08/01/2005

Once formated they would show as above. Otherwise, I will define a named
field, and go that way, but I have a lot of these headings to do, and
formatting them would be simplest way to get them to work, if possible.

Is there any sort of custom formatting that would do this?

Thanks!

PatK
 
D

Dave Peterson

I don't think you'll be able to do this by formatting alone--but you could use a
formula in a helper cell to display your FYQtr.

Personally, I think
FY2006-Q1
is nicer--I like 4 digit years and by putting the year first, I can sort nicely.

If you agree, you could use this formula:
="FY"&YEAR(A1)+(MONTH(A1)>=11)&"-Q"&INT(1+MOD(MONTH(A1)-11,12)/3)

If you disagree, you could use this formula:
="Q"&INT(1+MOD(MONTH(A1)-11,12)/3)&"FY"&RIGHT(YEAR(A1)+(MONTH(A1)>=11),2)
 
P

PatK

Thanks, Dave!! I will give it a try!

Dave Peterson said:
I don't think you'll be able to do this by formatting alone--but you could use a
formula in a helper cell to display your FYQtr.

Personally, I think
FY2006-Q1
is nicer--I like 4 digit years and by putting the year first, I can sort nicely.

If you agree, you could use this formula:
="FY"&YEAR(A1)+(MONTH(A1)>=11)&"-Q"&INT(1+MOD(MONTH(A1)-11,12)/3)

If you disagree, you could use this formula:
="Q"&INT(1+MOD(MONTH(A1)-11,12)/3)&"FY"&RIGHT(YEAR(A1)+(MONTH(A1)>=11),2)
Click to expand...
 
Top