Convert Date To Quarter in Excel

Do you know how to convert a date or a month into the quarter in Excel? In this blog, unlock multiple ways to convert the month into the quarter in Excel. All of the methods that we going to see are formula-based methods. We would be making complex nested formulas to get a quarter from the date.

Sample Dates (Convert Date to Quarter)

Below is the list of dates that would be used to understand this trick.

Convert Date to Quarter Sample Data

Methods to Convert Date to Quarter

As mentioned above, we would be learning different nested formulas to get the quarters from the date. Below is the list-

  2. CHOOSE with MONTH

As you can see from all the methods mentioned above, there is one common formula in all (viz. MONTH). 

The MONTH formula returns the month number. It takes only one attribute “cell reference” as its input parameter which means reference of the cell containing the date.

Refer to the below screenshot to understand how is the MONTH formula structured and what does it return as a result.

MONTH Formula to Extract Months

This result will be used in other formulas to get the quarter from this month.

Let us now start learning each of the methods one by one.

VLOOKUP with MONTH Formula

In order to use the VLOOKUP formula, we need to create one table in excel to map the quarter against each of the 12 months.

A support table something like this. It has been made on the assumption that we are using calendar month.

Support Table for VLOOKUP

Now, use this formula. Enter it in cell B2:


VLOOKUP Formula for Entering Quarter

As soon as you press the Enter key, you would notice that the excel returns the quarter number from the date. Excel did a very simple step. Firstly, it checks the month number in the date using MONTH(A2), then it searches for that month number in the left-most column (Column D) in the support table (D2:E13). Finally, it returns the corresponding value in column E against the relevant month.

Result of VLOOKUP in cell B2

Copy this formula in other cells in Column B to get the result for all of the dates.

VLOOKUP formula copied to other cells

CHOOSE with MONTH Formula

Unlike the VLOOKUP formula, we do not need to create any support table for mapping. The formula itself would include the mapping data.

Enter below formula in cell B2.


CHOOSE formula to get quarter from dates

The CHOOSE formula reads its first attribute (month number) and returns the value from the series of values starting from the second value based on the position.

As soon as you press Enter key on your keyboard, you would notice that excel returns “2” which is the value mentioned in the fourth position (April) in the CHOOSE formula starting from the second attribute. The below image would explain it in a nutshell.

Understanding CHOOSE Formula

Now, copy cell B2 and paste it to the other cells in column B. As a result, you would get the quarter for all the dates.

Result of CHOOSE Formula
Get Quarter from Dates in Excel


The ROUNDUP formula is a mathematical formula that rounds the value upwards towards the mentioned number of digits. Enter below formula in cell B2:


ROUNDUP with MONTH formula

The first attribute MONTH(A2)/3 divides the month number by 3 and rounds the resultant value upwards.

As soon as you press Enter, the formula would return the quarter from the date.

At last, copy the formula in cell B2 and paste it to the other cells in column B.


But, it is important to note that the above-mentioned methods are just a few that can be used. You can create more using other functions like using the combination of IF and MONTH formula and many more.

This brings us to the end of this blog. I hope that you have learned something out of this blog. Share your views and comments in the comment section below.

Leave a Comment