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
Bonus answer:
=MOD(INT((MONTH(A1)-1)/3)-1,4)+1
This should be more efficient:
=CONCATENATE("Q", INT(MONTH(A1)/4) + 1)