How can I obtain the year quarter from a date?

In Excel, how can I obtain the year quarter from a date, using a worksheet formula? I have a column with dates spanning multiple years.

• Jan-01 to Mar-31 = Q1
• Apr-01 to June-30 = Q2
• etc...

Bonus question: How can I shift the quarter so Q1 is April-June (Jan-Mar would be Q4).

Depending on how you have formatted your Date value, excel may automatically covert it to the "date code". this is the number of days since Jan 1, 1900 (Date code of 1). This number is then used by the different Date functions. Here is a suggested formula for simple quarter analysis.

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

This will just give you the quarter number. you can then use CONCATENATE to add text.

Here's a great tutorial with lots of examples:

http://www.cpearson.com/excel/DateTimeWS.htm

[EDIT]

As stated by @hyperslug, a better way to do this is to use the following:

=CONCATENATE("Q",ROUNDUP(MONTH(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)))/3,0))

This method shifts the date forward or backwards before getting a month value before dividing by 3. You can control the month the quarter starts by changing the "Month(A1)-3" section.

• Start Date of January use: "Month(A1)"
• Start Date of April use: "Month(A1)-3"
• Start Date of September use: "Month(A1)+4"

Addition and subtraction can both be used. It's unintuitive, but using subtraction moves the start quarter forward (-1 is February), and addition moves it backwards (+1 is December).

[/EDIT]

[EDIT] Note: This method works, but is not as elegant as the method above.

After some playing around, I found a way to answer your Bonus Question. You can use IF statements to return a value (text/int) based on the month. You just have to figure out what months are associated with what quarter.

=IF(AND(MONTH(A1)>=4,MONTH(A1)<7),"Q1",IF(AND(MONTH(A1)>=7,MONTH(A1)<10),"Q2",IF(AND(MONTH(A1)>=10),"Q3",IF(AND(MONTH(A1)>=1,MONTH(A1)<4),"Q4"))))

It's a dense equation that uses a combination of "IF", "AND", and "MONTH" functions. By modifying the month values (inside the AND statements), you can further control which value is returned. I used text strings, but you could easily modify them to fit your needs.

[/EDIT]

Hope this helps

• Or `=CONCATENATE("Q",ROUNDUP(MONTH(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)))/3,0))` Jan 27, 2010 at 19:27
• Whoa @hyperslug, that is a much better way to do it. All you have to do to change the start month of the 1st quarter is to add or subtract from the first data calculation "Month(A1)-3". I'm too used to working with IF/TEN statements. Feb 3, 2010 at 19:34

``````=MOD(INT((MONTH(A1)-1)/3)-1,4)+1
``````

This should be more efficient:

``````=CONCATENATE("Q", INT(MONTH(A1)/4) + 1)
``````
• More efficient than what? Why do you make that claim? Sep 4, 2015 at 13:55
• There is fewer computations (date conversion, etc.), the number of parameter is kept at the minimum level, and it's shorter. Although, I should use INT instead of FLOOR. I'll test it first. Sep 4, 2015 at 18:51
• it works, I've updated the answer. Sep 4, 2015 at 18:55

The following formula should work:

``````=IF(OR(MONTH(A1)=1,MONTH(A1)=2,MONTH(A1)=3),"Q1",IF(OR(MONTH(A1)=4,MONTH(A1)=5,MONTH(A1)=6),"Q2",IF(OR(MONTH(A1)=7,MONTH(A1)=8,MONTH(A1)=9),"Q3",IF(OR(MONTH(A1)=10,MONTH(A1)=11,MONTH(A1)=12),"Q4",""))))
``````

Good luck.