How to Convert a Date into Quarter

When working on the dates, you may often use quarters in the reports. There are 4 quarters in a year and each spans 3 months. For example, the 3 months of January, February and March are the first quarter in a calendar year.

In a fiscal year, the first quarter varies depending on the starting month, but the first 3 months is always the first quarter. For example, if a fiscal year starts from April, the month of April, May and June will be the first quarter.

To convert a date into a quarter in a calendar year, please use the following formula.

=CHOOSE(MONTH(A2), 1,1,1,2,2,2,3,3,3,4,4,4)

The Choose Function returns a value from a list with the index number specified. You can use the Month Function to get the month number as the index number.

If you want to convert a date into quarters in a fiscal year, which starts a month from February to December, you will need to carefully make the reference list as below.

=CHOOSE(MONTH(A2),1,1,1,2,2,2,3,3,3,4,4,4) – Jan the first month
=CHOOSE(MONTH(A2),4,1,1,1,2,2,2,3,3,3,4,4) – Feb the first month
=CHOOSE(MONTH(A2),4,4,1,1,1,2,2,2,3,3,3,4) – Mar the first month
=CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3) – Apr the first month
=CHOOSE(MONTH(A2),3,4,4,4,1,1,1,2,2,2,3,3) – May the first month
=CHOOSE(MONTH(A2),3,3,4,4,4,1,1,1,2,2,2,3) – Jun the first month
=CHOOSE(MONTH(A2),3,3,3,4,4,4,1,1,1,2,2,2) – Jul the first month
=CHOOSE(MONTH(A2),2,3,3,3,4,4,4,1,1,1,2,2) – Aug the first month
=CHOOSE(MONTH(A2),2,2,3,3,3,4,4,4,1,1,1,2) – Sep the first month
=CHOOSE(MONTH(A2),2,2,2,3,3,3,4,4,4,1,1,1) – Oct the first month
=CHOOSE(MONTH(A2),1,2,2,2,3,3,3,4,4,4,1,1) – Nov the first month
=CHOOSE(MONTH(A2),1,1,2,2,2,3,3,3,4,4,4,1) – Dec the first month

In the real reports, other than the quarter numbers, you can also combine with the Year and the word of "Quarter" or "Q", please use the following formula or check how to convert a date into fiscal year.

="Quarter " & CHOOSE(MONTH(A2),1,1,1,2,2,2,3,3,3,4,4,4) & ", " & YEAR(A2)

Leave a Reply