Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Quarters from Dates
I am looking at showing the quarter a selective date is in i.e. 9-Dec-09
would be 4Qtr09.s Any code that can do this without having to go in to do it manually? The amount of data in the column is also constantly changing so I would need the code to loop until the end of the data. Any help would be appreciated. Date Quarter 9-Dec-09 4Qtr09 1-Jan-10 1Qtr10 The data in these columns are generated using a macro and will not always have the same number of rows. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Quarters from Dates
you can use VLOOKUP. create a column next to your date. in that column,
format the date to be MM-YYYY. Create a VLOOKUP table that has one column of MM-YYYY and the other column of NQtrYY "dwake" wrote: I am looking at showing the quarter a selective date is in i.e. 9-Dec-09 would be 4Qtr09.s Any code that can do this without having to go in to do it manually? The amount of data in the column is also constantly changing so I would need the code to loop until the end of the data. Any help would be appreciated. Date Quarter 9-Dec-09 4Qtr09 1-Jan-10 1Qtr10 The data in these columns are generated using a macro and will not always have the same number of rows. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Quarters from Dates
I use this formula to show the fiscal year and quarter:
="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3) Where # represents the first month of the fiscal year. So if the fiscal year starts on July 1st, then I'd use: ="FY"&YEAR(A1)-(MONTH(A1)<7)&"-Q"&INT(1+MOD(MONTH(A1)-7,12)/3) ======== In your case, since Jan 1st is the start of the fiscal year: ="FY"&YEAR(A1)-(MONTH(A1)<1)&"-Q"&INT(1+MOD(MONTH(A1)-1,12)/3) or ="FY"&YEAR(A1)&"-Q"&INT(1+MOD(MONTH(A1)-1,12)/3) (I like the FY####Q# format--it makes it easier to sort in nice order.) ======== Ps. If you're doing this to group dates in a pivottable, you don't need to. Pivottables have a group by quarter feature (and it uses calendar quarters like you!). dwake wrote: I am looking at showing the quarter a selective date is in i.e. 9-Dec-09 would be 4Qtr09.s Any code that can do this without having to go in to do it manually? The amount of data in the column is also constantly changing so I would need the code to loop until the end of the data. Any help would be appreciated. Date Quarter 9-Dec-09 4Qtr09 1-Jan-10 1Qtr10 The data in these columns are generated using a macro and will not always have the same number of rows. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Quarters from Dates
For the calendar quarters:
1/1 - 3/31 = 1 4/1 - 6/30 = 2 7/1 - 9/30 = 3 10/1 - 12/31 = 4 =CEILING(MONTH(A1)/3,1)&TEXT(A1,"Qtryy") Or, because some international settings handle the TEXT function differently: =CEILING(MONTH(A1)/3,1)&"Qtr"&RIGHT(YEAR(A1),2) -- Biff Microsoft Excel MVP "dwake" wrote in message ... I am looking at showing the quarter a selective date is in i.e. 9-Dec-09 would be 4Qtr09.s Any code that can do this without having to go in to do it manually? The amount of data in the column is also constantly changing so I would need the code to loop until the end of the data. Any help would be appreciated. Date Quarter 9-Dec-09 4Qtr09 1-Jan-10 1Qtr10 The data in these columns are generated using a macro and will not always have the same number of rows. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lumping dates into quarters by year | Excel Worksheet Functions | |||
Calculating quarters of a year. | Excel Worksheet Functions | |||
sumif between dates (quarters) | Excel Worksheet Functions | |||
Formatting of dates into quarters | Excel Discussion (Misc queries) | |||
How do I Turning Dates into Quarters | Excel Worksheet Functions |