| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lumping dates into quarters by year
I have a list of dates extending out for the next 8 years and I need to put
them in the quarter/year category - i.e., 3Q09 for a date that is 10/12/09. Is there a formula to convert dates to this type of format? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lumping dates into quarters by year
try:
=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200835 "Anna Wood" wrote: I have a list of dates extending out for the next 8 years and I need to put them in the quarter/year category - i.e., 3Q09 for a date that is 10/12/09. Is there a formula to convert dates to this type of format? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lumping dates into quarters by year
I like this formula in general--if the fiscal year starts on the first of month
number #: ="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) So if the fiscal year starts on October 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<10)&"-Q"&INT(1+MOD(MONTH(A1)-10,12)/3) I also like this style of result: FY2009-Q1 It makes sorting by that column easier. Anna Wood wrote: I have a list of dates extending out for the next 8 years and I need to put them in the quarter/year category - i.e., 3Q09 for a date that is 10/12/09. Is there a formula to convert dates to this type of format? -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lumping dates into quarters by year
Thank you - it worked perfectly. I would have never gotten there on my own.
"Gary''s Student" wrote: try: =CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200835 "Anna Wood" wrote: I have a list of dates extending out for the next 8 years and I need to put them in the quarter/year category - i.e., 3Q09 for a date that is 10/12/09. Is there a formula to convert dates to this type of format? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How should I get the # of quarters in a year. | Excel Worksheet Functions | |||
| Calculating quarters of a year. | Excel Worksheet Functions | |||
| year quarters === Financial Year | Excel Discussion (Misc queries) | |||
| how do I convert a dates in a year quarters in a year? | Excel Discussion (Misc queries) | |||
| How do I Turning Dates into Quarters | Excel Worksheet Functions | |||
Linear Mode
