I was recently working on a project where I had dates in Excel and I had to find out what quarter of the year it was based on the date.
For the purpose of this tutorial, let’s consider the following:
- Quarter 1 – Jan, Feb, and Mar
- Quarter 2 – Apr, May, and Jun
- Quarter 3 – July, Aug, and Sep
- Quarter 4 – Oct, Nov, and Dec
In case you’re working with financial data where the quarter starts from April, then you can adjust the formulas accordingly (also covered later in this tutorial).
Now let’s see how to use simple formulas to find out the quarter from the date.
Find Quarter From Date in Excel (Calendar Year)
Suppose you have the data set as shown below and you want to calculate the Quarter number for each date.
Below is the formula to do that:
=ROUNDUP(MONTH(A2)/3,0)
The above formula uses the MONTH function to get the month value for each date. The result of this would be 1 for January, 2 for February, 3 for March, and so on.
This month number is divided by 3 so that for all the months in Quarter 1, we get a value less than or equal to 1, and for all the months in Quarter 2, we get a value that is greater than 1 and less than or equal to 2, and so no.
And then ROUNDUP function is used so that we get the same quarter value for all the months in that quarter.
Quite straightforward!
Find Quarter From Date in Excel (Financial/Accounting Year)
In accounting and financial analysis, Quarter 1 begins and April and Quarter 4 ends in March.
In such a case, we need to adjust our formula to get the correct result.
Below is the formula that will give the correct quarter value for dates in a financial year:
=IF(ROUNDUP(MONTH(A2)/3,0)-1=0,4,ROUNDUP(MONTH(A2)/3,0)-1)
In this formula, I have used the same ROUNDUP function (that I used in the previous example to calculate the quarter for a calendar year), and subtracted 1 from it.
It gives the right result for all the quarters except quarter 4 (where it would give 0 instead of 4).
And to fix this simple issue I use the IF formula to check if the quarter value is 0 or not, and if it is 0, then I simply make the formula return 4 instead.
You can use the same logic to get the quarter value from the date in case your quarter starts from July or October.
So this is how you can use a simple formula to get the quarter value from a date in Excel.
I hope you found this tutorial useful.
Other Excel tutorials you may also like:
