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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*