Converting a Date to a Calendar Quarter

This is a guest post from Joe Helstrom, CPA.
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.