Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

up vote 6 down vote accepted

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

share|improve this answer
    
Great thanks a lot - using it right away! –  mtone Jan 27 '10 at 19:08
1  
Or =CONCATENATE("Q",ROUNDUP(MONTH(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)))/3,0)) –  hyperslug Jan 27 '10 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. –  Doltknuckle Feb 3 '10 at 19:34

Bonus answer:

=MOD(INT((MONTH(A1)-1)/3)-1,4)+1
share|improve this answer
    
Nice one, thanks! –  mtone Jan 27 '10 at 19:36

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.

share|improve this answer

Isn't it easy enough to just use:

=roundup(MONTH(A1)/3) 

where A1 is the cell with the date?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.