How to Calculate Business Quarters in Excel Not Based on Calendar Year

Written by susan hare
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Calculate Business Quarters in Excel Not Based on Calendar Year
A quarter is one fourth of a year, or three months. (old calendar image by Allyson Ricketts from

Microsoft Excel is a productivity spreadsheet application that supports data entry and formula calculations. It can be useful to perform calculations on dates and their respective quarters. Using quarters allows data to be condensed for trending or summary purposes. Some businesses may need to perform calculations on fiscal quarters rather than calendar quarters. For instance, if you wanted to trend profits in a year-over-year graph, it would be too cluttered to include all 12 months for each year, but it might be useful to see which quarters were more productive.

Skill level:


  1. 1

    Open a new workbook in Excel and enter a list of random dates that can be used for testing.

    You can copy these example dates to your sheet beginning in cell A1:

    1/4/2010 2/11/2010 3/21/2010 4/28/2010 6/5/2010 7/13/2010 8/20/2010 9/27/2010 11/4/2010 12/12/2010

  2. 2

    Determine the number value of the month in which your "year" starts (e.g. January is 1 and November is 11). For this example, the year will start in October (month 8).

  3. 3

    Subtract one from your month value. In this example, the result would equal seven (7).

  4. 4

    Substitute your new value into the following equation:

    =MOD(CEILING(22+MONTH(<date cell>) - <month value minus one> - 1,3)/3,4)+1

    For this example that would be: =MOD(CEILING(22+MONTH(A1)-7-1,3)/3,4)+1

  5. 5

    Copy and paste the equation into cell B1 in your Excel worksheet.

  6. 6

    Click and hold the mouse button down on cell B1 and drag your mouse cursor down to highlight all the cells from there to B10.

  7. 7

    Hit the keys "Control" ("Ctrl") and D at the same time to copy the formula down to all the cells.

    Your worksheet should now look like the following data:

    01/04/2010 2 02/11/2010 3 03/21/2010 3 04/28/2010 3 06/05/2010 4 07/13/2010 4 08/20/2010 1 09/27/2010 1 11/04/2010 2 12/12/2010 2

Tips and warnings

  • To calculate quarters based on the calendar year, you can use the same formula and substitute a zero for the year value.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.