accounting year quarter from date

Many times you may need to summarize the data from date wise to quarterly basis. For this, you need to calculate the quarter from the date value.

In Excel, there is no inbuilt function to get quarter from a date.

In this tutorial, you will learn 5 ways to get quarter from a date in Excel.

Quarter from date using Ceiling function

Calendar year quarter

In calendar year 1st quarter begins with Jan month.

In the below image you can see cell B2 formula is

=”Q” & CEILING(MONTH(A2),3)/3 this formula outputs Q1

Formula working:-

MONTH(A2) will give 1. CEILING(1,3) will output 3.

Thus 3/3 will give 1 and this is concatenated with the letter “Q”.

Similarly, if the month is 5th then CEILING(5,3) will output 6 and 6/3 is 2 (quarter 2).

Watch the video related to Ceiling function in Excel

Financial Year Quarter

In financial year 1st quarter begins with Apr month.

In the below image you can see cell B2 formula

=”Q” & IF(MONTH(A2)<4,4,(CEILING(MONTH(A2),3)/3)-1)

Note:- If you want only quarter number then don’t concatenate the letter “Q”.

Get quarter using Choose function

You also use Choose function to get quarter from a Date.

In the below image cell B2 formula for calendar year quarter is

=”Q”& CHOOSE(MONTH(A2),1,1,1,2,2,2,3,3,3,4,4,4)

For financial year quarter use the below formula

=”Q”& CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3)

The below image shows the working of the Choose formula for the financial year quarter.

If the month is 1 then 1st value from the series is 4 hence 4 will be output.

If the month is 12 then the 12th value from the series is 3 hence 3 will be output.

Watch the video related to Choose function in Excel

Using approximate Vlookup to get quarter

To get quarter from date using Vlookup you need to first set a reference table.

In the below image reference table is on the right side.

Here I have not used the “Q” letter prefix.

Cell B2 formula for calendar year quarter

=VLOOKUP(MONTH(A2),$F$1:$G$6,2,TRUE)

Cell C2 formula for financial year quarter

=VLOOKUP(MONTH(A2),$F$1:$H$6,3,TRUE)

If you don’t want to refer a table then you can embed the table as an array in Vlookup formula like below.

So, cell B2 formula for calendar year quarter becomes

=VLOOKUP(MONTH(A2),{1,1;4,2;7,3;10,4;12,4},2,TRUE)

Embed Table in Lookup Formula

Custom function (VBA UDF) to get quarter from date

Quarter function syntax

Quarter(date,RootMonthNo)

The quarter function takes two parameters.

Date is date

RootMonthNo is 1st month number of the 1st quarter (1st quarter starting month).

Example:- In the calendar year, 1st month number of the 1st quarter is 1 (Jan).

In Financial year 1st month number of 1st quarter is 4 (Apr).

Cell B2 formula to get calendar year quarter

=Quarter(A2,1)

Cell C2 formula to get financial year quarter

=Quarter(A2,4)

Below is the code for the Quarter custom function (VBA UDF). You need to paste this code in the standard module.

Learn more about creating Excel VBA User Defined Function

	
Function Quarter(dt As Variant, rootMnthNo As Variant) As Byte
'' dt is date
''rootMnthNo is 1st month number of 1st qrtr
Dim mnth As Byte, nxtMnth As Byte
Dim cntr As Byte
mnth = Month(dt)
nxtMnth = rootMnthNo
For cntr = 1 To 12
     Quarter = Application.WorksheetFunction.Ceiling(cntr, 3) / 3
     If mnth = nxtMnth Then Exit Function
     If nxtMnth = 12 Then
        nxtMnth = 1
     Else
        nxtMnth = nxtMnth + 1
     End If
Next cntr
End Function