| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How do I Turning Dates into Quarters
I have a spreadsheet with dates in it (in format mm/dd/yyyy) that I would
like to translate into the appropriate fiscal quarter. For example... Any date between 1/1/2005 and 3/31/2005 = Quarter1 Any date between 4/30/2005 and 6/30/2005 = Quarter2... Any ideas? |
|
#2
|
|||
|
|||
|
Hi Rob
You can use my Add-in to filter the quarter http://www.rondebruin.nl/easyfilter.htm You can filter in place or copy to other sheet/workbook -- Regards Ron de Bruin http://www.rondebruin.nl "Rob V" <Rob wrote in message ... I have a spreadsheet with dates in it (in format mm/dd/yyyy) that I would like to translate into the appropriate fiscal quarter. For example... Any date between 1/1/2005 and 3/31/2005 = Quarter1 Any date between 4/30/2005 and 6/30/2005 = Quarter2... Any ideas? |
|
#3
|
|||
|
|||
|
="Quarter"&INT((MONTH(A1)+2)/3)
HTH Jason Atlanta, GA -----Original Message----- I have a spreadsheet with dates in it (in format mm/dd/yyyy) that I would like to translate into the appropriate fiscal quarter. For example... Any date between 1/1/2005 and 3/31/2005 = Quarter1 Any date between 4/30/2005 and 6/30/2005 = Quarter2... Any ideas? . |
|
#4
|
|||
|
|||
|
Thanks! One other complication...
Is there a formula that would work if the year-end is 9/30? For example: 10/1/04 - 12/31/04 = Quarter 1 1/1/05 - 3/31/05 = Quarter 2 4/30/05 - 6/30/05 = Quarter 3 7/1/05 - 9/30/05 = Quarter 4 "Jason Morin" wrote: ="Quarter"&INT((MONTH(A1)+2)/3) HTH Jason Atlanta, GA -----Original Message----- I have a spreadsheet with dates in it (in format mm/dd/yyyy) that I would like to translate into the appropriate fiscal quarter. For example... Any date between 1/1/2005 and 3/31/2005 = Quarter1 Any date between 4/30/2005 and 6/30/2005 = Quarter2... Any ideas? . |
|
#5
|
|||
|
|||
|
="Quarter"&ROUNDUP((MONTH(A1)+3)/3,0)-(MONTH(A1)9)*4
HTH Jason Atlanta, GA -----Original Message----- Thanks! One other complication... Is there a formula that would work if the year-end is 9/30? For example: 10/1/04 - 12/31/04 = Quarter 1 1/1/05 - 3/31/05 = Quarter 2 4/30/05 - 6/30/05 = Quarter 3 7/1/05 - 9/30/05 = Quarter 4 "Jason Morin" wrote: ="Quarter"&INT((MONTH(A1)+2)/3) HTH Jason Atlanta, GA -----Original Message----- I have a spreadsheet with dates in it (in format mm/dd/yyyy) that I would like to translate into the appropriate fiscal quarter. For example... Any date between 1/1/2005 and 3/31/2005 = Quarter1 Any date between 4/30/2005 and 6/30/2005 = Quarter2... Any ideas? . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How sort dates just by day/month and NOT year if all 3 given in ce | Excel Worksheet Functions | |||
| Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
| due dates | New Users to Excel | |||
| I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) | |||
| Filtering with dates | Excel Discussion (Misc queries) | |||
Linear Mode
