Calculating Quarters from Dates

[dwake]

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.

 

[BCDS]

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

 

[Dave Peterson]

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!).

 

[T. Valko]

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)