We offer a simple formula that returns the quarter of a given date. There is no built-in function that can do this in Excel.
Enter the formula shown below:
=ROUNDUP(MONTH(A1)/3,0)
=ОКРУГЛВВЕРХ(МЕСЯЦ(A1)/3;0)
Explanation:
- Function ROUNDUP(x;0) always rounds up х to the nearest whole number.
- Function MONTH (MONTH) returns the serial number of the month from a date.
- In this example, the formula boils down to:
ОКРУГЛВВЕРХ(5/3;0)=ОКРУГЛВВЕРХ(1.666667;0)= 2 - Everything is correct! May belongs to the second quarter.
Let’s see if this formula works for the rest of the months:
It is not hard to see that the first three values (months) in the column B are rounded up to 1 (Quarter 1), the next three values are rounded up to 2 (Quarter 2), and so on.