A lambda to encapsulate the calculation of the Financial Quarter and present it in four ways.

Development Rationale

The purpose in developing this lambda is to simplify the derivation of the financial quarter from a date.  This not an overly complicated thing, but it is useful to have it "tidied away" in a lambda to make it easy to implement in workbooks where that is required.

About the Lambda Calculations

The process of calculating the financial quarter for a given date is fairly straight-forward, and most efficiently can be done with a little trick and a bit of arithmetical manipulation.  You need two pieces of information, the date for which the calculation is to be rendered and the month in which the financial year ends.  The latter is perhaps most simply provided as a number, although an implementation could be done that took the month name, or a month abbreviation.  But working like that with text would then add some issues if different languages were involved.

The calendar month is easily calculated using the MONTH function.  But how do you calculate the financial month?  In this method, we will move the date back in time by the same number of months that the financial year-end extends into the calendar year.  This effectively calculates an aligned calendar date from which the month can be found.  The next step is to round that month up to the lowest quarter end month greater than or equal to to the month number, and then a simple division translates it into a quarter number.

For example, what quarter is the date 15 May 2023 in, if the financial year-end is September?  May is the 8th month of a September year-end.  But we need to do a straightforward calculation.  Here is how we would approach this:

The financial year-end extends into the calendar year by 9 months.  So if we "pushed it back" in time 9 months, September would become December - the twelfth month of the calendar year.  If we did the same thing to May, and push it back 9 months, it would be August.  Now we can do calendar year calculations that have a relative relationship to our original dates.  August is the eight of the year, it is in the quarter ending September (9th month), and therefore in the 3rd quarter (9/3).

Here, I will layout the workings using line wrapping, so each step of the LET function can be read and analysed:

1  =LAMBDA(as_at_date,fy_end_month,[fy_format],
2  LET(fyEnd,λFinancialYearEnd(as_at_date,fy_end_month),
3  fyResult,λFinancialYear(as_at_date,fy_end_month,fy_format),
4  relMonth,MONTH(EDATE(1*as_at_date,-MONTH(fyEnd))),
5  qtrResult,CEILING.MATH(relMonth,3)/3,
6  fyResult&" "&"Q"&qtrResult))

In line 1 above, the lambda is defined, with three arguments: as_at_date, fy_end_month and the optional [fy_format].  fy_end_month is expected to be a number in the range 1..12, fy_format is expected to be a number in the range 1..4

In line 2, the LET function in the fourth argument of the LAMBDA encases the step-by-step calculation of the required result.  Initially, a local value called fyEnd is derived by calling an associated lambda function from this library - λFinancialYearEnd, which calculates the year end date.

In line 3, the local value fyResult is derived by calling an associated lambda function from this library - λFinancialYear, which calculates the financial year in the desired format.

In line 4, the local value relMonth is calculated as the calendar MONTH of a date which has been adjusted backwards as described above to relate each financial month to a calendar month.  Due to the way that EDATEhandles horizontal arrays of dates for its start_date argument, 1*as_at_date cures the issue with horizontal arrays that would lead to a #VALUE! error.  The alternative handling to correct for EDATE's expectations is significantly more complex, so we have settled on this simple fix.  The fix is only required if a horizontal array is passed to as_at_date.  Single values or vertical arrays do not have the issue.

In line 5, the local value qtrResult is calculated by rounding the relMonth up to the next quarter-end month using CEILING.MATH.  So 1,2 and 3 round to 3; 4, 5 and 6 round to 6; and so on. that quarter end month is divided by three to get the quarter number.  .Quarter end months are 3, 6, 9, 12.  Divided by 3, they give 1, 2, 3, 4!

Finally, in line 6, the result returned by the LET function is calculated, which is the result of the LAMBDA: it concatenates the formatted year from fyResult with the quarter number..

Defined Name Settings

To create the lambda, simply use the define name¹ feature in Excel to define this named lambda in the workbook where it will be used.  The required settings are shown below and may simply be copied from here and pasted into the Define Name dialog box.

Syntax of λFinancialQuarter

=λFinancialQuarter(as_at_date,fy_end_month,[fy_format])

where:
   as_at_date is a reference to a range, a nested expression that returns an array or reference to a range, or a constant value with a date or dates whose financial years is/are to be derived

   fy_end_month is a reference to a range, a nested expression that returns an array or reference to a range, or a constant value 1 through 12, representing the financial year ending in months January through December respectively

   [fy_format] is a reference to a range, a nested expression that returns an array or reference to a range, or a constant value 1 through 3, indicating the format of the value returned.
        1 requests the financial quarter be returned as a text string made up of the year and quarter for example "2024 Q1"
        2 requests the financial quarter be returned as a text string, prefixed with "FY" with the quarter appended, for example "FY 2024 Q2"
        3 requests the financial quarter be returned as a text string showing the beginning and ending calendar years with the quarter appended, for example "2023/24 Q3".  Note that for December year-ends, the result is returned as for fy_Format = 1
        4 requests the financial quarter be returned as a text string showing two digits each of the beginning and ending calendar years in the financial year separated by a slash, for example "23/24. Q4"

NB: This lambda function requires the λFinancialYearEnd and λFinancialYear functions to be available in the workbook, since it depends upon them to calculate the financial year-end date and formatted financial year!

Sample File

An example file, showing the definition and use of this lambda is available here: Public Lambda Samples.xlsx.

 Version History

¹  The required settings are shown below.  Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.

² You may change the name of the lambda and drop the prefix.  The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source.  The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.