Convert Dates To Fiscal Quarters in Google Sheets

Published on

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))
Dynamic Lookup Table to Assign Q1, Q2, Q3, and Q4 in Google Sheets

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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.