An easy formula that returns the quarter for a given date. There’s no built-in function in Excel that can do this.
How to do that..? This tip involves little math.
Step #1: We will just use the month of the date and divide it by 3.
So the conversion looks like
1| January | 1/3 = 0.33
2 | February | 2/3 = 0.67
3 | March | 3/3 = 1
4 | April | 4/3 = 1.33
5 | May | 5/3 = 1.67
6 | June | 6/3 = 2
7 | July | 7/3 = 2.33
8 | August | 8/3 = 2.67
9 | September | 9/3 = 3
10 | October | 10/3 = 3.33
11 | November | 11/3 = 3.67
12 | December | 12/3 = 4
Months 3, 6, 9 &12 looks good, but what about the rest with decimal values? If the month is April, 4 divided by 3 = 1.33 and we know that this should be in the second quarter.
Step #2: Use Roundup function to handle the decimal values
The Excel Roundup function “rounds up” a number.
=Roundup(cell reference, Number of digits).
If the number of digits argument is zero, it rounds up to the nearest integer.
So, if we have a date of 1/23/2015 in cell A3, we first need to know the month. The Month function will provide that. In cell B3, type =Month(A3). The result is 1.
We’ll combine the Month function into the Roundup function. Using the same cell, B3, type =Roundup(Month(A3)/3,0).
This function takes the numeric month, divides it by 3 and rounds it up to the nearest integer. In this case 1 divided by 3 = 0.33 which is rounded up to 1 (First quarter).
Try it on other dates. If, in cell A4, the date is 4/23/2015, in cell B4 type, =Roundup(Month(A4/3,0). Your answer is 2 (Second quarter).
Download the sample file: Formula to Convert a Date to Calendar Quarter.xls
If you have a series of dates that you want to convert to quarters, just copy the formula.
- How to Calculate Age in Excel Accurately?
- 10 Tricks to Make Data Entry Easier in Microsoft Excel
- How to VLOOKUP with Multiple Criteria in Multiple Column
- List of Excel Keyboard Shortcuts By Category