Excel formula: Get quarter from date

Generic formula

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

Explanation

If you want to calculate the quarter (i.e. 1,2,3,4) from a date, you can use the ROUNDUP function together with MONTH.

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

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

How this formula works

In this case, the formula 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.

The ROUNDUP function works like the ROUND function, except that ROUNDUP will always round numbers 1-9 upwards to a given number of digits.

Adding a Q

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

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

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

Calculating quarter of the year from a date

Author

Dave Bruns

Related formulas

Get fiscal quarter from date

If you want to calculate the fiscal quarter from a date, and the fiscal quarter starts in a month other than January, you can use a formula based on the CHOOSE function. Note: if you just need to calculate a "normal" quarter based on a quarter...

Get fiscal year from date

To get a fiscal year from a date, you can use a formula based on the YEAR and MONTH functions. In the example shown, the formula in D5 is: = YEAR ( B5 ) + ( MONTH ( B5 ) >= C5 ) How this formula works By convention a fiscal year is denoted by the...

Related functions

Excel MONTH Function

The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the...

Excel ROUNDUP Function

The Excel ROUNDUP function returns a number rounded up to a given number of decimal places. Unlike standard rounding, where numbers less than 5 are rounded down, ROUNDUP always rounds numbers 1-9 up.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.