How to Calculate Quarter from a Date (Excel Formula)

A quarter means 1/4th portion or 25% of a whole. It could be anything – quantity, money, calendar year, or even an apple pie.

From here we know that each calendar year has 12 months, and each quarter is three months (12/4=3) 📆

Dividing a year into 4 is easy – but let’s see if Microsoft Excel can do it too. In this tutorial, I will walk you through the methods to find the quarter for a given date.

Here is the practice workbook for this tutorial that I recommend you download and follow me along the way.

Table of Contents

Calculate quarter from a date for a calendar year

We don’t have something called a quarter function in MS Excel to find the quarter for a given date in Excel. However, this must not and will not stop us from automating the quarter calculation in Excel.

For a calendar year, the quarter division starts in January and takes the following shape 👇

• First Quarter: January, February, March
• Second Quarter: April, May, June
• Third Quarter: July, August, September
• Fourth Quarter: October, November, December

With this, here is a list of random dates from a year. To find the quarter for each of these dates based on a normal calendar year:

Step 1) Write the Month function as below.

The MONTH function returns the month for a given date. Since February is the second month of the calendar year, it returns 2.

Step 2) Divide the results of the MONTH function by 3 per the following formula:

Since each quarter has 3 months, we will divide the month number by 3. So, for February, this will be 2nd Month divided by 3 = 0.6667. For April, this will be the 4th Month divided by 3 = 1.33, and so on.

So, we can say that:

• For all months between 1st to 3rd, this number will be under or equal to 1.
• For all months between the 4th to 6th, this number will be more than 1 and less than or equal to 2.
• For all months between the 7th to 9th, this number will be more than 2 and less than or equal to 3.
• For all months between 10th to 12th, this number will be more than 3 and less than or equal to 4.

With that, we almost have the quarter for each date already calculated. The only problem left now is the decimal places 🤔

Step 3) Round up the results produced by the above formula to zero decimal places by using the ROUNDUP function.

The ROUNDUP function will every number up to the next nearest integer.

So, for example, for all months between 1st to 3rd, the answer without the ROUNDUP function would have been under or equal to 1. The ROUNDUP function will round all such results up to 1.

Step 4) Drag and drop the formula using the fill handle to the whole list of dates.

And there you go – the quarter for each date is calculated, and a quick look into it reveals that the results are accurate.

Step 5) To take the results a step ahead, you can concatenate the above formula with the text string “Quarter” and “of 2024” as below.

Here’s what the final quarter calculation looks like

Pretty easy and quick, no?

Calculate quarter from a date for a financial/fiscal year

Calculating quarters based on a normal calendar year is straightforward enough.

However, calendar years are not applicable everywhere. For instance, tax laws follow a fiscal year of July to June. So as many companies have a financial year starting from July up till June.

For a fiscal/financial year of July to June, the quarter division changes as follows;

• First Quarter: July, August, September
• Second Quarter: October, November, December
• Third Quarter: January, February, March
• Fourth Quarter: April, May, June

Can we find quarters for a fiscal year? Sure, we can. But for that, we’d have to tweak our formula.

This formula gets a little long so bear with me till the end 🤯

I have added some dates to our dataset to include a date from each month of the year.

In a normal calendar year, the first quarter starts from the 1st Month. Whereas, for a fiscal year, the 1st quarter starts from July (6 months ahead). So we will add 6 to the MONTH function.

Step 1) Write the MONTH function nested within the ROUNDUP function as below.

Everything remains the same as above except for the addition of 6 to the month number.

• Results for the months of January to June are accurately classified in Quarter 3 and Quarter 4.
• However, we get a result > 4 (i.e., 5) for the months of July, August, and September.
• And results > 5 (i.e., 6) for the months of Quarter 2 (October, November, and December).

Step 2) To fix these results, we will simply write an IF condition.

Telling Excel that if the result of the above function exceeds 5, give back 2. Since we know that we only get the results >5 for the months of Quarter 2.

Step 3) Now, write the value if false for this IF function, for which we will nest in another IF function.

This time telling Excel that if the result of the above function exceeds 4, give back 1. Since we know that we only get the results >4 for the months of Quarter 1.

Step 4) Finally, give in the value if false for the nested IF function.

This value of false translates to the fact that if the results of the specified conditions neither exceed 5, nor 4, simply run the month function devised in step 1 and give back the results.

Pro Tip!

Here’s a quick summary of how this formula works:

This is the formula:

• Check if the results of this formula exceed 5. If yes, return 2.
• If not, again check if the results of this formula exceed 4. If yes, return 1.
• If not, run the formula itself and give back whatever results it yields.

Hope it makes sense now 💪

Step 5) Press Enter to have your results ready.

Step 6) Drag down the formula across all the dates to see Excel calculate the fiscal year-based quarter for each date.

Practice the formula for different dates to understand the logic it operates on 💡

Once you understand it, you can easily rewrite it for any year, April to March, October to September, and anything.

Conclusion

This tutorial discusses how you can calculate quarters from a given date in Excel. We practiced how to calculate it for a normal calendar year, as well as for a fiscal year.

I hope you enjoyed tuning different functions to Excel to produce unique results – frankly speaking, this is my favorite part of dynamic Excel.

Once you’re good at the basics of Excel functions, the possibilities of using Excel for your good are simply endless. If you also want to learn how to become better than the best at using Excel, continue reading my Excel tutorials. For now, I recommend you learn How to Drag a Formula Down in Excel (Fastest Way) and How to Count Cells With Specific Text Using a Formula.