Convert Dates To Fiscal Quarters in Google Sheets

GiGiKing·March 13, 2024·0 comments

By just providing the fiscal year ending month name and year, we can convert dates in a column into fiscal quarters in Google Sheets.

We can use such outputs to group data for financial reports/statements.

As you may already know, there are four fiscal quarters in a year often referred to in financial statements as Q1, Q2, Q3, and Q4.

My Google Sheets formula will work even if the calendar year (1st January to 31st December) is used as the fiscal year.

For example, if you feed the month name December in one cell and 2021 in another cell, the formula will convert dates in Jan-Mar to Q1, Apr-Jun to Q2, Jul-Sep to Q3, and Oct-Dec to Q4 (All months are in 2021).

If you input the month name January and year 2022, the formula will assign Q1 to Feb-Apr, Q2 to May-Jul, Q3 to Aug-Oct, and Q4 to Nov-Jan (Only January is in 2022).

But we can’t use it if the fiscal year ends on the same day of the week each year.

For example, if the fiscal year ends on the last Saturday of September every year.

It’s because the formula requires whole months.

How to Convert Dates To Fiscal Quarters in Google Sheets

Sample Data: A1:A (dates to convert).

Other User Inputs: D1:E1 (Fiscal year ending month and year).

Formula to Convert Dates To Fiscal Quarters - Google Sheets

Before showing the formula in cell B1, which converts dates to fiscal quarters, we require to make a dynamic lookup table for it to work.

I have hidden that table purposefully in the above screenshot to avoid clutter.

Here are the steps involved.

Dynamic Lookup Table for Assigning Q1, Q2, Q3, and Q4 to Dates

There are two steps.

Step # 1

First, insert the following two DATE-related formulas in cells D2 and D3, respectively.

D2:

=eomonth(date(E1,month(D1&1),1),0)

D3:

=eomonth(edate(eomonth(date(E1,month(D1&1),1),0),-12),0)+1

What are their roles?

The first formula returns the end of the month date of the given month name and year in D1:E1, whereas the second one returns the same day last year + 1.

So, ultimately they return the fiscal year start date (D3) and end date (D2) using the given month text (D1) and year (E1).

The role of the highlighted part of the formula is to convert a month name in text to a month number.

Step # 2

The following array formula in cell E2 returns the sequence of end of the months based on D3 (start date) and D2 (end-date).

=ArrayFormula(eomonth(EDATE($D$3,SEQUENCE(12,1,0,1)),0))

Finally, in F2, insert the following combo to assign quarters to the dates returned by the above formula.

=ArrayFormula(value(flatten(SUBSTITUTE(sequence(4,1)," ","",SEQUENCE(1,3)))))

Here the SEQUENCE returns the numbers 1 to 4. The SUBSTITUTE repeats all of them thrice horizontally. The FLATTEN makes them horizontal.

The VALUE function converts text output to numbers.

Formula to Convert Dates To Fiscal Quarters in Google Sheets

Here is the array formula in cell B1 that converts dates to fiscal quarters in Google Sheets.

=ArrayFormula({"Quarter";ifna(vlookup(eomonth(A2:A,0),E2:F13,2,0))})

Do you want a quick formula explanation? Then here you go!

The above is a Vlookup array formula.

Array Formula Syntax:– ArrayFormula(VLOOKUP(search_keys, range, index, [is_sorted]))

Search_Keys: eomonth(A2:A,0) (end of the months of the dates in column A)

Range: E2:F13 (dynamic lookup table)

Index: 2 (output column, i.e., quarters)

Is_Sorted: 0 (unsorted lookup table).

Can we combine step 1 and 2 formulas in the cell B1 formula and thus avoid the helper cells/range?

Yep! It may help (new B1 formula).

=ArrayFormula({"Quarter";ifna(vlookup(eomonth(A2:A,0),{eomonth(EDATE(eomonth(edate(eomonth(date(E1,month(D1&1),1),0),-12),0)+1,SEQUENCE(12,1,0,1)),0),value(flatten(SUBSTITUTE(sequence(4,1)," ","",SEQUENCE(1,3))))},2,0))})

Empty the range E2:F13 and D2:D3.

That’s all! This way, we can convert dates to fiscal quarters in Google Sheets.

Resources

[EOMonth]: End of Month
[ArrayFormula]: Array Formula
*[Vlookup]: Vertical Lookup

GiGiKing

Related posts