How to create a Quarter formula in Excel

by | Jul 10, 2018 | Excel Tips & Tricks

How to get quarter from date

There is no specific Quarter formula in Excel. But an Excel quarter formula can be constructed by combining ROUNDUP and MONTH functions with custom number formatting.

Syntax

=ROUNDUP( MONTH( date value ) /3, 0 )

Steps

  1. Start with =ROUNDUP( function
  2. Continue with MONTH( function
  3. Select the date cell B3
  4. Type ) to close the MONTH function
  5. Divide the month value by 3 /3,
  6. Type 0 to round without decimals
  7. Type ) to close the ROUNDUP function and finish the formula
  8. Copy and paste down the formula
  9. Select the quarter range
  10. Press Ctrl + 1 to open Format Cells
  11. Select Custom in list
  12. Type Q# to display numbers with "Q" prefix

How

Firstly, we need to get the month index from a date value using the MONTH function. Next step is to divide the month index by 3, because each quarter contains 3 months. As a result, dividing the month index by 3 returns an approximate number that represents the corresponding quarter.

Finally, the ROUNDUP function will round the number into nearest integer which is 1 through 4 in this case.

=ROUNDUP(MONTH(B3)/3,0)

To make the Excel quarter formula presentation more user friendly, we can change the number format to represent the value as a quarter by adding letter "Q" in front:

  1. Select the cell (or range)
  2. Press Ctrl +1 or right-click and select Format Cells
  3. Select a built-in formats or select Custom and enter yours

In this example, we apply "Q#" number format which converts the numeric quarter value (1) to text presentation (Q1). For more information and examples on custom number formatting, please refer to the article: Number Formatting in Excel – All You Need to Know