Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number

Most businesses account their annual operations on a January to December schedule. For those businesses, their quarterly calendar looks like this:
Quarter 1: January, February, March.
Quarter 2: April, May, June.
Quarter 3: July, August, September.
Quarter 4: October, November, December.

For this common accounting cycle, and as shown in the following picture, you can determine a month’s quarter number by listing each month’s first calendar day. Next, in cell B2 and copied down to cell B13 is this formula:
=INT((MONTH(A2)-1)/3)+1



As you know, not every business runs its operations on a traditional January to December cycle. The below table covers each of the 12 possible scenarios for listing a month’s quarter number, depending on the first month of that company’s first fiscal quarter.

All it takes is one formula…
="Q"&MOD(CEILING(23-COLUMN()+MONTH($A5),3)/3,4)+1
…entered into cell B5, copied across to cell M5, and then B5:M5 copied down to B16:M16.

I added the preceding “Q” for each quarter number, which is optional.