To calculate the quarter (i.e. 1,2,3,4) for a given date, you can use the ROUNDUP function together with the MONTH function. In the example shown, the formula in cell C5 is:


The result is 1, since January 31 is in the first quarter.

Generic formula



In this example, the goal is to return a number that represents quarter (i.e. 1,2,3,4) for any given date. In other words, we want to return the quarter that the date resides in.

ROUNDUP formula solution

In the example shown, the formula in cell C5 is:


The ROUNDUP function works like the ROUND function, except that ROUNDUP will always round numbers 1-9 up to a given number of digits, supplied as the num_digits argument. In this case, because we want to get back an integer, we use zero for num_digits.

Working from the inside out, the MONTH function first extracts the month as a number between 1-12, then divides this number by 3:


The result is then rounded up to the nearest whole number using the ROUNDUP function:

=ROUNDUP(0.3333,0) returns 1

The result is 1, since 0.3333 rounded up to the next whole number is 1.

Adding  "Q"

If you want the quarter number to include a "Q" you can concatenate the numeric result from ROUNDUP to the "Q". The formula in D5 is:

="Q"&ROUNDUP(MONTH(B5)/3,0) // returns "Q1"

The result is the letter "Q" prepended to the number:

Calculating quarter of the year from a date

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.