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.

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).**

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**

- 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

## 2 Comments

If the date format is mm/dd/yy, above formula is not work. Can you advise how to change the formula. Thanks,

U r doing wonderful job,,, being qualified IT professional I would like to convey to you my good wishes to move forward.