Select Page

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

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