Explanation
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:
=ROUNDUP(MONTH(B5)/3,0)
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:
=MONTH(B5)/3
=1/3
=0.3333
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:
Related formulas
Get fiscal quarter from date
Get fiscal year from date
Related functions
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 DATE function.
ROUNDUP Function
The Excel ROUNDUP function returns a number rounded up to a given number of decimal places. Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDUP rounds all numbers up....
