How to Convert a Date to Calendar Quarter?

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 Convert Date to Calendar Quarter00

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.

Syntax
=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).

How to Convert a Date to a Calendar Quarter01

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.

Also Read

 

Show Comments

2 Comments

  1. Chris
  2. Rajesh Sinha

Leave a Reply