|

» Calculating the Quarter Number

To calculate the quarter number for a calendar year:

Enter the formula:

=INT((MONTH(A2)-1)/3)+1

To calculate the quarter number for a fiscal year ending in September:

Enter the formula:

=MOD(CEILING(22+MONTH(A2)-9-1,3)/3,4)+1

Note: The number 9 in the formula represents the number of the last month of the fiscal year (September).
Screenshot // Calculating the Quarter Number

Rate This Tip
 1 2 3 4 5
Rating: 3.41     Views: 107171
Formatting Quarters ?
Joe Dowski  Posted on: 31-12-1969
I'm sure the tip shown works but I have used a cheap & dirty vlookup on a table that I create. The table has 2 columns, column 1 has numbers 1-12 which represent months, column 2 reads: 1 1 1 2 2 2 3 3 3 4 4 4 I just vlookup the month in whatever source date I'm using to the month in my lookup table and return the column 2 value. Works for me !! J.
Quarter Number
Jaroslav Havelka  Posted on: 31-12-1969
I also use lookup function, but I do not create extra table with month and quarter numbers. I write it directly into formula, for example:
=HLOOKUP(A1,{1,2,3,4,5,6,7,8,9,10,11,12;1,1,1,2,2,2,3,3,3,4,4,4},2)
where A1 is month number
J.
Name
Comment Title