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
- Start with =ROUNDUP( function
- Continue with MONTH( function
- Select the date cell B3
- Type ) to close the MONTH function
- Divide the month value by 3 /3,
- Type 0 to round without decimals
- Type ) to close the ROUNDUP function and finish the formula
- Copy and paste down the formula
- Select the quarter range
- Press Ctrl + 1 to open Format Cells
- Select Custom in list
- 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:
- Select the cell (or range)
- Press Ctrl +1 or right-click and select Format Cells
- 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
