Excel Unplugged

Formula to get quarters from dates in Excel

We have a column of dates in Excel, and our goal is to get Q1, Q2,… or QTR1, QTR2,… or Quarter 1, Quarter 2,… . I think you get the picture 🙂

First we will show the simple formula to get quarter number from dates. Then we will show how to add any prefix to that (like Q, QTR or Quarter…), and then we will show how to modify your formula starting with any other month. Let’s say July 1st is the beginning of Q1. Let’s dive into it.

Getting quarter number from dates

You actually get the quarter number from month number which you extract from the date. There are two basic ways to do this, presuming the date is in cell A1

=ROUNDUP(MONTH(A1)/3,0)

=INT((MONTH(A1)-1)/3)+1

As you can see the differences are purely cosmetically but they both give you a quarter number. If you already have a month number in a column, you can replace MONTH(A1) with a reference to the cell with the month number.

Adding a string in front of the quarter number

Now to add a string like Q, QTR or Quarter in front of the number

= “Q”&ROUNDUP(MONTH(A1)/3,0)

= “Q”& INT((MONTH(A1)-1)/3)+1

So you add the string or better yet the prefix you want in front of the previous formula, dress it up in apostrophes (“) and add a ampersand (&) to append the number to the string. If you want the result to be Quarter 1, the formula would be = “Quarter “&ROUNDUP(MONTH(A1)/3,0). You may notice an extra space after the Quarter but inside the apostrophes, which is purely cosmetic.

Starting Q1 with any month you want

Now this is where it gets tricky, but it is done with one general formula. First the formula (this is a formula that will start Q1 with July)…

=ROUNDUP(MOD(MONTH(A1)+5.1,12)/3,0)

There are three new things here, and they will be explained individually, but first, the value that determines which month will be the first month of Q1. In our case it’s 5.1 because we want to start with July. Here is a table of values to use in the formula (the rest of the formula stays the same) with corresponding starting months.

January

February

March

April

May

June

July

August

September

October

November

December

10.1

9.1

8.1

7.1

6.1

5.1

4.1

3.1

2.1

1.1

0.1

 

Now for the rest of the formula. You take the month number and add a value to that. If the value is 7.1 (so may is the first month of Q1), and you take a date that starts in November, you will get 11 + 7.1. This will give you 18.1 and as for the month numbers, the 18th month is unheard of, so we will use the MOD(number,devisor) function, where the devisor will be 12. This means that 18.1 will actually be 6.1 after the MOD function gets through it. As you divide this by 3 you will get 2.033333 and after the ROUNDUP function is done, this will be 3, so November is in Q3 if May is the start of Q1 which is correct.

Hope this article was helpful and it got you closer to eternal happiness :). I recently wrote about another way to get quarter from dates. Zou can read about it here.

Comments 7

  1. Sarah says:

    I’m trying to do an Excel formula to assign a quarter (Q1-Q4) to each month, where the month is a 3-character text column (Jan-Dec).

    I put together the following formula. It’s working for Jan (Jan = Q1), but then it doesn’t work for any following month, showing a #VALUE error. Ideas?

    =IF(B2=”Jan”,”Q1″,(OR(IF(B2=”Feb”,”Q1″,(OR(IF(B2=”Mar”,”Q1″,(OR(IF(B2=”Apr”,”Q2″,(OR(IF(B2=”May”,”Q2″,(OR(IF(B2=”Jun”,”Q2″,(OR(IF(B2=”Jul”,”Q3″,(OR(IF(B2=”Aug”,”Q3″,(OR(IF(B2=”Sep”,”Q3″,(OR(IF(B2=”Oct”,”Q4″,(OR(IF(B2=”Nov”,”Q4″,(OR(IF(B2=”Dec”,”Q4″,))))))))))))))))))))))))))))))))))

    I tried replacing “OR” with “AND” – didn’t work.

    1. Hi Sarah,

      This will give you the right result

      https://excelunplugged.files.wordpress.com/2014/11/qrt.xlsx

      Use any of the two given formulas.

      1. Sivakumar A says:

        Hi . Its really great .

        But i want another condition to be added in this formula Like- If A1=2015, or If A1=2016

        could you pls help me out.//

        1. Hi,
          I’m not totally sure I understand the question but if you want to get a result in a Q3-16 fashion then you need to modify your formula in one of two ways…

          If you only have the year (2016) then you add &”-“&Right(Text(A1,”0000″)) to the existing quarter formula

          If you have a date value, then you do a &”-“&Text(A1,”YY”)

          Hope this helps…

    2. Dipesh Bhalavat says:

      =CHOOSE(MONTH(A1),”Q1″,”Q1″,”Q1″,”Q2″,”Q2″,”Q2″,”Q3″,”Q3″,”Q3″,”Q4″,”Q4″,”Q4″)

  2. Meni Porat says:

    Hi Gašper,

    On my blog I have uploaded some time ago a post which explains how to get the yearly quarter in Excel.
    This post suggests 5(!) different methods for the problem (actually, even more).

    Even though the post is in Hebrew, I’m sure you’ll be able to understand.

    Here’s the link:
    http://meniporat.blogspot.co.il/2013/08/5_1669.html

    Best Regards,
    Meni Porat

    1. Meni Porat says:

      Perhaps sending you to the relevant post on my blog was not very clever, because it is written in Hebrew…
      So here are 5 additional formulae that’ll yield the same result:

      =CEILING(MONTH(A1)/3,1)

      =MROUND(CEILING(MONTH(A1)/3,0.5),1)

      =ROUND(ROUNDUP(MONTH(A1)/3,0),0)

      =ROUNDUP(ROUNDDOWN(MONTH(A1)/3,2),0)

      =ROUND(CEILING(MONTH(A1)/3,0.5),0)

      Best Regards,
      Meni Porat

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.