Friday, October 14, 2011
Converting a Date to a Calendar Quarter
I thought you would be interested in this as Excel is not that user-friendly when it comes to dates.
How to Convert a Date to a Calendar Quarter
This tip involves some math. We're going to use just the numeric month portion of the date and divide by three. So, if the month is 3 (March), 3 divided by 3 = 1 (First quarter). If the month is 6 (June), 6 divided by 3 = 2 (Second quarter). If the month is 9 (September), 9 divided by 3 = 3 (Third quarter).
What happens when the math is a little messier? If the month is 2 (February), 2 divided by 3 = .66666. We know that this should be in the 1st quarter. If the month is May, 5 divided by 3 = 1.6666 and we know that this should be in the second quarter. The Roundup function needs to be used.
The Excel Roundup function "rounds up" a number. The syntax for Roundup is =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 2/12/2011 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 2.
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 2 divided by 3 = .66666 which is rounded up to 1 (First quarter).
Try it on other dates. If, in cell A4, the date is 5/17/2011, in cell B4 type, =Roundup(Month(A4/3,0). Your answer is 2 (Second quarter).
If you have a series of dates that you want to convert to quarters, just copy the formula. You have a quick, easy way to convert dates to calendar quarters.
For courses that address these and other Excel tips and provide CPE to CPAs, visit http://www.cpaselfstudy.com/ or excel-diva.com for non-CPE Excel courses.