# 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.

### Gašper Kamenšek

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.

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

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