How to Get QUARTER from a Date in Excel


In Excel, there is no direct function to get a quarter from a date. But you can create a formula for this.

Yes, you can use a formula to calculate a quarter from a date. Even there is more than one method which you can use.

Alright, as you know there is a total of four quarters in a year but the thing is you can get a quarter from a date in different ways.

The first is, assuming year on the normal calendar basis or the second one on the fiscal calendar basis.

And in today’s post, I’m going to show you methods to get the quarter number using both of the calendars and I’ll also share more than one method with you for this.

So let’s get started.


5 Different Ways to Get Quarter from a Date

Here I’ve listed top 5 ways which you can use to get a quarter from a date. Each of these methods is unique and you use any of these according to your need.

1. Get Quarter by using ROUNDUP and MONTH Functions

Using a combination of ROUNDUP and MONTH is the best way to find the quarter of a date. It returns quarter as a number (like 1,2,3,4).

Here’s the formula.


excel quarter number using round month formula

Here we are using 26-May-2018 as a date and the formula returns 2 in the result. You can use below formula if you want to add a Q before the quarter number.

excel quarter number using round month formula with q

How this formula works

This formula works in three parts.

In the first part, we have used the MONTH function and referred to the date which returns the month number.

After that, in the second part, we have divided that month number with 3 which gives us a number with decimals.

excel quarter number using round month formula part second

At last, in the third part, we have used ROUNDUP function to round (that number with decimals) which gives us the actual quarter.

excel quarter number using round month formula part three

❤️ Here’s is the real thing:

You know there are 4 quarters and twelve months.


And if you divide all the month numbers with three (yes, just with 3) you’ll get something like below.

excel quarter number formula basics

And when you round them up you’ll always the get the quarter number for the month.

2. Get Quarter by using Fiscal Calendar

In the above example, we have used a simple formula to have a quarter from a date, but sometimes you need to use a fiscal calendar.

What is a Fiscal Year?

A fiscal year (FY) is a 12 month period that a company or government uses for accounting purposes and preparing financial statements. A fiscal year may not be the same as a calendar year…

…learn more

And for a fiscal calendar quarter calculation, we need to use a different formula and in this formula, we need to combine CHOOSE and MONTH function.

Here’s the formula:

excel quarter number using fiscal year formula

(Here I’ve used the Indian fiscal year which starts on 1 April and ends on 31 March)

It returns 1 in the result as the month is May and our year starts from April and May is the second month in the year comes under the first quarter.

How this formula works

Here we have combined two different functions and it works in two parts.

excel quarter number using fiscal year formula works

In the first part, we have used the MONTH function to get month number from the date and here it returns 5.

excel quarter number using fiscal year formula first part with month function

After that, in the second part, we have used to choose the function to get a number according to the month number return by the MONTH function.

excel quarter number using fiscal year formula second part choose

If you look, in CHOOSE function we have specified 12 different numbers (4,4,4,1,1,1,2,2,2,3,3,3) so when MONTH returns a number CHOOSE returns a number from this list.

Here’s the deal:

For the first three numbers, we have 4 that means if it returns 1,2, or 3 (Jan-Mar) we will get 4 and for next three months, we will get 1 (That’s because our fiscal year is starting from April).

3. Convert a Date into a Quarter using VBA Code

Here we are using a VBA code to simply convert a date into its quarter number.

Here ‘s the code:

Sub convert2Quarter()

Dim Rng As Range

For Each Rng In Selection

If IsDate(Rng.Value) = True Then

Rng.Value = DatePart("q", Rng.Value)

Rng.NumberFormat = "General"

End If

Next Rng

End Sub

To use this code all you need to do is to add it into your VB editor.

code to get quarter number excel vbe

And then simply select the cells where you need to convert a date into a quarter number and finally run this code.

here's how this code works...

When you run this code, firstly it checks whether the cell has a date or not, and if it’s a date then it converts it into the quarter number.

We have used two different VBA functions here:

  1. ISDATE: To check whether the cell has a date.
  2. DATEPART: To convert a date into quarter.

4. Get Quarter Start date from a Date

Let’s forget about the quarter number and use the starting date of the quarter.

That means if we have date 26-May-2018 so instead of having quarter number 2 we can have 01-Apr-2018 which is the starting date of the first month of the quarter.

Here’s the formula which we need to use:

excel quarter start date formula

When you enter this formula it simply returns the starting date of the quarter.

How this Formula Works

This formula is made up of four different parts which give you the starting date of a quarter.

excel quarter start date formula works

In the first part, we have used MONTH function which returns the month number for the date.

So when MONTH returns 5 as the month number (then minus one from it), in the second part, FLOOR round down it to 3 and then add one into it so can we get 4 which is the first month (April) the same quarter.

excel quarter start date formula floor math

After that, in the third part, we have used the YEAR function to get the year from the actual date.

And in the fourth part, we have simply combined the DATE function create a date combining values come from the first two parts.

5. Get Quarter End date from a Date

OK, so you need the end date of a quarter instead of the start date or the number and for this, the formula which we can use is:

excel quarter formula get end date

For 26-May-2018, it returns 30-Jun-2018 which is the last date of the quarter.

How this Formula Works

In this formula, we have four different parts.

excel quarter formula get end date works

In the first part, we have combined INT and MONTH function.

MONTH returns the month number of the date and then we have divided it with 3. After that INT round down it to the nearest integer.

excel quarter formula get end date first part

In the end, we have multiplied it with 3 to get 6 (June) which is the end month of the quarter and then added 1 into it.

In simple words, this part of the formula simply converts month of the actual date to the next quarter’s first month.

In the second part, we have used the YEAR function to get the year from the actual date.

excel quarter formula get end date second part

After that, in the third part, simply used 1 complete the date with DATE function.

Sorry! one more thing.

In the fourth, minus 1 from the entire formula, so that we can have the last date of the quarter instead of the first date of the next quarter.

excel quarter formula get end date third part

Sample File


By just combining functions, you can create a simple formula to calculate a quarter from a date.

And in this post, we have explored four formulas + VBA code for this.

Frankly speaking, #1 is the most used and simplest way to find a quarter of date but you can use rest all if you want.

I hope you found this post useful, but now, tell me one this.

Do you know any other method which to calculate the quarter number from a date?

Share with me in the comment section, I’d love to hear from you and please don’t forget to share this tip with your friends. I’m sure they will appreciate it.

You must Read these Next

  1. Text to Date: We do need some methods which we can use to convert a text to an actual date with the right format...
  2. End of the Month Date: The best way to calculate the last date of the month is by using EOMONTH Function. But...
  3. Get Day Name from a Date: You can use TEXT function for extracting day name from a date. Let’s say you have...
  4. Get Month from a Date: There are a few times when we need to use only a part from a date. Take an example...
  5. Highlight Dates Between Two Dates: Let’s say you have a start date 17-Jan-2017 and an end date 19-Mar-2017...
  6. Sum Values Between Two Dates: Let’s say, you are working in a trading company and your boss asks you to get the...
  7. Data Validation with Date Range: Well, this can be done by using a custom formula based on the AND...
  8. Group Dates in a Pivot Table: With this, you can instantly create insightful and ready to present reports. When you work...
  9. DATEDIF: We can use it like we use all the Excel functions. The only difference is when you try to insert it...

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.


7 thoughts

Leave a Comment

Your email address will not be published.

  1. Is there a way to “Q” using the CHOOSE function? It doesn’t seem to work when I’ve tried it.

  2. Very nice website Puneet.
    Clear and logical descriptions.

    I downloaded your Quarters workbook and found
    I needed to add your URL to a cell so I could return to your website.

    I would suggest either a reference worksheet with your information or
    a pasted link in a convenient cell.

    Kind Regards,

  3. Hi

    How could i calculate the actual dates of a tax year

    ie it should show for the current date – 06/04/2019 to 05/04/2020

    Any help would I would be grateful.



  4. Brilliant and useful (as always)…
    Here’s how to calculate start of quarter with Excel 2007 which supposedly doesn’t support your formula by using CHOOSE instead:

  5. Formula in Method 1 was as below:-


  6. Hi Puneet,
    Thanks for all the good work that you do.

    Below are two more methods that can be used to find quarter of the date. Both are slightly complex than the first one, but still thought of sharing as I thought of them.
    Please note that the date was in cell A1.

    1) Using Month in nested If:-


    2) Using Vlookup (but for this we need to have below mentioned table. I made this table from H5 to I8)
    1 q1
    4 q2
    7 q3
    10 q4