Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formating Fiscal Quarters, rather than Months
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 |
#2
|
|||
|
|||
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) PatK wrote: 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 -- Dave Peterson |
#3
|
|||
|
|||
Thanks, Dave!! I will give it a try!
"Dave Peterson" wrote: 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) PatK wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
How do i change 15 months to read 1 year and 3 months? | Excel Discussion (Misc queries) | |||
Fiscal year total from running 12 months | Excel Worksheet Functions |