Calculate Quarter from Date
Using quarters of a year for reporting is a very common concept in all kinds of businesses. Unfortunately (and curiously) Excel doesn’t have a function for calculating quarter from date. But there is a workaround. You can easily calculate quarter from date with a small formula.
Business quarters can be calculated with several different approaches. I was calculating quarters of dates with an atrociously long formula before. You can see it here. Then I came across a much easier and shorter formula.
Obviously, most logical way of calculating quarter from date is using month of that date. Quarters are:
Quarter Month Nr Month Name
1 1,2,3 January, February, March
2 4,5,6 April, May, June
3 7,8,9 July, August, September
4 10,11,12 October, November, December
Here is how we will calculate quarter from date:
- Extract month using MONTH Function
- Determine which quarter that month belongs to by dividing it by 3.
- Round resulting number to an integer using ROUNDUP Function
=ROUNDUP(MONTH(12.02.2014)/3;0) will result as 1.
Here are some examples that shows the formula to calculate quarter from date in action: