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