For example, you’re a project manager tracking the start dates of various projects. You need to organize these dates by quarter to plan your team’s workload throughout the year.
In Excel, there is no direct function to get a quarter from a date. But you can create a formula for this. Yes, you can use a formula to calculate a quarter from a date. Even there is more than one method that you can use. As you know there is a total of four quarters in a year but the thing is you can get a quarter from a date in different ways.
The first is, assuming a year on a normal calendar basis or the second one on a fiscal calendar basis. And in this’s tutorial, I’m going to show you methods to get the quarter number using both of the calendars and I’ll also share more than one method with you for this.
Get Quarter by using ROUNDUP and MONTH Functions
Using a combination of ROUNDUP and MONTH is the best way to find the quarter of a date. It returns a quarter as a number (like 1,2,3,4). Here’s the formula.
=ROUNDUP(MONTH(A1)/3,0)
Here we are using 26 May 2018 as a date and the formula returns 2 in the result. You can use the below formula if you want to add a Q before the quarter number.
="Q-"&ROUNDUP(MONTH(A1)/3,0)
How this formula works
This formula works in three parts. In the first part, we used the MONTH function and referred to the date which returns the month number.
After that, in the second part, we divided that month number by 3 which gives us a number with decimals.
At last, in the third part, we used the ROUNDUP function to round (that number with decimals) which gives us the actual quarter.
Here’s the real thing: You know there are 4 quarters and twelve months. Right? And if you divide all the month numbers by three (yes, just with 3) you’ll get something like the below.
And when you round them up you’ll always get the quarter number for the month.
Get Quarter by using the Fiscal Calendar
In the above example, we have used a simple formula to have a quarter from a date, but sometimes you need to use a fiscal calendar.
What is a Fiscal Year?
A fiscal year (FY) is a 12-month period that a company or government uses for accounting purposes and preparing financial statements. A fiscal year may not be the same as a calendar year…
And for a fiscal calendar quarter calculation, we need to use a different formula and in this formula, we need to combine CHOOSE and MONTH functions. Here’s the formula:
=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)
(Here I’ve used the Indian fiscal year which starts on 1 April and ends on 31 March)
It returns 1 in the result as the month in May and our year starts from April and May is the second month in the year that comes under the first quarter.
How this formula works
Here we have combined two different functions and it works in two parts.
In the first part, we have used the MONTH function to get the month number from the date and here it returns 5.
After that, in the second part, we have used to choose the function to get a number according to the month number returned by the MONTH function.
You can learn more about CHOOSE from here. If you look, in CHOOSE function we have specified 12 different numbers (4,4,4,1,1,1,2,2,2,3,3,3) so when MONTH returns a number CHOOSE returns a number from this list. Here’s the deal:
For the first three numbers, we have 4 which means if it returns 1,2, or 3 (Jan-Mar) we will get 4 and for the next three months, we will get 1 (That’s because our fiscal year is starting from April).
Use the CEILING Function to Get the Quarter
Like the ROUNDUP, it also rounds up the month number divided by 3 to the nearest integer to find the quarter.
=CEILING(MONTH(A1)/3, 1)
Like all the other formulas we have discussed, it helps you get the quarter of the year a date falls into.
First, it finds the month number of the date. Then, it divides this month’s number by 3. Finally, the CEILING function rounds up the result to the nearest whole number, the quarter.
Use QUOTIENT and IF to Check Quarter
QUOTIENT can divide one number by another and then give you the result without any remainder. Below is the formula that you can use by combining it with the IF.
=IF(MONTH(A1)<=3, 1, IF(MONTH(A1)<=6, 2, IF(MONTH(A1)<=9, 3, 4)))
This formula checks the quarter of the year for a date in cell A1. It checks the month of the date: if it’s March or before, it returns 1, indicating the first quarter. If it’s June or before, it returns 2 for the second quarter.
If it’s September or before, it returns 3 for the third quarter. Otherwise, it returns 4 for the fourth quarter.
Get Start Date of the Quarter from a Date
Let’s forget about the quarter number and use the starting date of the quarter.
That means if we have the date 26-May-2018 so instead of having quarter number 2 we can have 01-Apr-2018 which is the starting date of the first month of the quarter. Here’s the formula which we need to use:
=DATE(YEAR(A1),FLOOR.MATH(
When you enter this formula it simply returns the starting date of the quarter.
How this Formula Works
This formula is made up of four different parts which give you the starting date of a quarter.
In the first part, we have used the MONTH function which returns the month number for the date.
So when MONTH returns 5 as the month number (then minus one from it), in the second part, FLOOR round down it to 3 and then adds one into it so can we get 4 which is the first month (April) the same quarter?
After that, in the third part, we used the YEAR function to get the year from the actual date. And in the fourth part, we have simply combined the DATE function to create a date combining values that come from the first two parts.
Get End Date of a Quarter from a Date
OK, so you need the end date of a quarter instead of the start date or the number and for this, the formula which we can use is:
=DATE(YEAR(A2),((INT((MONTH(A2)-1)/3)+1)*3)+1,1)-1
For 26-May-18, it returns on 30-Jun-18 which is the last date of the quarter.
How this Formula Works
In this formula, we have four different parts.
In the first part, we have combined INT and MONTH functions. MONTH returns the month number of the date and then we have divided it by 3. After that INT rounds down it to the nearest integer.
In the end, we multiplied it by 3 to get 6 (June) which is the end month of the quarter, and then added 1 into it.
In simple words, this part of the formula simply converts the month of the actual date to the next quarter’s first month.
In the second part, we used the YEAR function to get the year from the actual date.
After that, in the third part, simply used 1 complete the date with the DATE function.
Sorry! One more thing. In the fourth, minus 1 from the entire formula, so that we can have the last date of the quarter instead of the first date of the next quarter.
Convert a Date into a Quarter using VBA Code
Here we are using a VBA code to simply convert a date into its quarter number. Here’s the code:
Sub convert2Quarter()
Dim Rng As Range
For Each Rng In Selection
If IsDate(Rng.Value) = True Then
Rng.Value = DatePart("q", Rng.Value)
Rng.NumberFormat = “General”
End If
Next Rng
End Sub To use this code all you need to do is to add it to your VB editor.
And then simply select the cells where you need to convert a date into a quarter number and finally run this code.
Here’s how this code works…
When you run this code, firstly it checks whether the cell has a date or not, and if it’s a date then it converts it into the quarter number. We have used two different VBA functions here:
- ISDATE: To check whether the cell has a date.
- DATEPART: To convert a date into a quarter.
Check this out: Macro Codes for VBA New Comers | What is VBA in Excel
Create a Custom Function for Quarter
Function GetQuarterFromDate(rng As Date) As String
Dim monthNumber As Integer
monthNumber = Month(rng)
Select Case monthNumber
Case 1 To 3
GetQuarterFromDate = "Q1"
Case 4 To 6
GetQuarterFromDate = "Q2"
Case 7 To 9
GetQuarterFromDate = "Q3"
Case 10 To 12
GetQuarterFromDate = "Q4"
End Select
End Function
Is there a way to “Q” using the CHOOSE function? It doesn’t seem to work when I’ve tried it.
Very nice website Puneet.
Clear and logical descriptions.
I downloaded your Quarters workbook and found
I needed to add your URL to a cell so I could return to your website.
I would suggest either a reference worksheet with your information or
a pasted link in a convenient cell.
Kind Regards,
David
Hi
How could i calculate the actual dates of a tax year
ie it should show for the current date – 06/04/2019 to 05/04/2020
Any help would I would be grateful.
Thankyou
AL
Brilliant and useful (as always)…
Here’s how to calculate start of quarter with Excel 2007 which supposedly doesn’t support your formula by using CHOOSE instead:
=DATE(YEAR(A1),CHOOSE(MONTH(A1),1,1,1,4,4,4,7,7,7,10,10,10),1)
Woohoo! thanks for sharing.
this works GREAT . THank you
Formula in Method 1 was as below:-
IF(AND(MONTH(A1)=1),”Q1″,IF(AND(MONTH(A1)>=4,MONTH(A1)=7,MONTH(A1)<=9),"Q3","Q4")))
Hi Puneet,
Thanks for all the good work that you do.
Below are two more methods that can be used to find quarter of the date. Both are slightly complex than the first one, but still thought of sharing as I thought of them.
Please note that the date was in cell A1.
1) Using Month in nested If:-
IF(AND(MONTH(A1)=1),”Q1″,IF(AND(MONTH(A1)>=4,MONTH(A1)=7,MONTH(A1)<=9),"Q3","Q4")))
2) Using Vlookup (but for this we need to have below mentioned table. I made this table from H5 to I8)
1 q1
4 q2
7 q3
10 q4
VLOOKUP(MONTH(A1),H5:I8,2,1)
.