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.

3 comments on “Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number”
1. sandeep kothari says:

Dear Tom, This is very useful. Will you please share the excel sheet (soft copy) through email?

2. MPSS says:

Pl. explain the full logic of the formula. Why 23 is added? Why Ceiling function is used? etc.

• Tom Urtis says:

Working the formula inside out, which is how you would do it anyway.

Example, in cell I10 is this formula:
`COLUMN()+MONTH(\$A10)`
The column is I which means it is column 9, and in cell A10 the month is June which is 6. The sum of 9 and 6 is 15.

The 23 is a number that works with the `CEILING` function to get the next highest number that is divisible by 3 (the significance argument of the `CEILING` function).
`23-COLUMN()+MONTH(\$A10)`
is the same as
`23-9+6=20`.

20 divided by 3 equals 6.667.

The `CEILING` function controls the calculation so that the number it returns is divisible by 3.

`CEILING(20,3) equals 21`.

In the case of the formula in cell I10, the `CEILING` function evaluates `CEILING(20,3)` and returns 21.

21 divided by 3 equals 7.

The `MOD` function returns the remainder after a number is divided by the divisor.

The portion of the formula
`MOD(CEILING(23-COLUMN()+MONTH(\$A10),3)/3,4)`
returns 3, same as
`MOD({21}/3,4)=3`.

The 4 is the divisor for the count of quarters, which is always 4.

The number 1 is added to that calculation to arrive at the quarter number for that month name in column A of the formula’s row.