**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.

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

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

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.