Excel formula: Get quarter from date

Generic formula

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

Summary

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)

Explanation

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

The choose function uses the first argument to "select" remaining elements. For example, in a scheme where 1 = small, 2 = medium, and 3 = large, this formula will "map" the number 2 to "medium". = CHOOSE ( 2 , "small" , "medium...

Get fiscal year from date

By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year begins in July, then the date August 1, 2018 is in fiscal year 2019. The year function first returns the year from the date in B5, 2017. To this result, 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 rounds all numbers 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.