A lambda to encapsulate the calculation of the Financial Year in which a date falls.

Development Rationale

The purpose in developing this lambda is to simplify the derivation of the financial year from a date.  The financial year is not overly complicated to calculate, but it is useful to have it "tidied away" in a lambda to make it easy to implement in workbooks where that is required.  It also simplifies formulas that need to show the financial year in headings with different appearances

About the Lambda Calculation

The process of calculating the financial year for a given date is fairly straight-forward.  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.

If the month in which the date falls is the same as or earlier than the month in which the financial year ends, then the calendar year (easily calculated with the YEAR function) and the financial year are the same.  If however, the month in which the date falls is in a month after the financial year ends, then the financial year is one greater than the calendar year!  This is quite straight forward to implement, but if we want to build in some checking to ensure the solution is robust and able to handle changing assumptions around when year-end falls for different enterprises, then a little more work is required to avoid a hard-wired solution.

But if we are going to the trouble of building a lambda for it, we may like to stretch ourselves a little and cater for some options.  For instance, in some settings, financial year 2024 may be just shown as 2024.  It may have a prefix, such as FY 2024.  And it may be shown as 2023/24 or 23/24.  There are no doubt other options, but these are perhaps the most common that my clients use.  We will add an extra option as a number to specify how the year is returned.  Bear in mind that if the financial year-end month is December, as it is for a lot of US domiciled enterprises, then the financial year and the calendar year are the same, and the third and fourth options canvassed above would not apply, as we wouldn't want to show 2024/24, I think.

Since we have some extra steps to traverse, we would be wise to use a LET function to streamline things, and make the layout easier and to avoid lots of deep nesting and redundancy.

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,YEAR(fyEnd), 
4  modifiedFormat,IFS((fy_format<1)+(fy_format>4),1,(fy_format>2)*(MONTH(fyResult)=12),1,TRUE,fy_format),
5  slashResult,fyResult-1&"/"&RIGHT(fyResult,2),
6  SWITCH(modifiedFormat,1,fyResult,2,"FY "&fyResult,3,slashResult,RIGHT(slashResult,5))))

In line 1 above, the lambda is defined, with three arguments capitalised as for standard functions: 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 and if it outside this range, it will be set to 12, fyFormat is expected to be a number in the range 1..4 and if omitted will default to 1.

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 fyResult is derived by calling an associated lambda function from this library - λFinancialYearEnd, which calculates the year end date.

In line 3, the local value modifiedFormat uses an IFS function to limit the value of the format argument to being between 1 and 4.  Again addition of comparison is used to replace an OR function so that it correctly calculates for arrays of inputs (unlikely - but possible).  It also tests whether the MONTH of fyEnd is 12, and if fy_format > 2 sets the format to 1 since formats 3 and 4 can't be sensibly used with a December year-end.

In line 4, the local value fyResult is calculated as the YEAR of fyEnd.

In line 5, the local value slashResult calculates a version of the fyResult in the format yyyy/yy, using string concatenation and the RIGHT function.

finally, in line 6, the result returned by the LET function is calculated, which is the result of the LAMBDA: it calls a SWITCH function, and based on the value in modifiedFormat, returns one of the four possible answers designed in this lambda.

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 λFinancialYear

=λFinancialYear(asAtDate,fyEndMonth,fyFormat)

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 4, indicating the format of the value returned.  If omitted or the value is out of this range, the format 1 is the default.
         1 requests the financial year be returned as a number, for example 2024
         2 requests the financial year be returned as a text string, prefixed with "FY", for example "FY 2024"
         3 requests the financial year be returned as a text string showing the beginning and ending calendar years, for example 2023/24.  Note that for December year-ends, the result is returned as for fyFormat = 1
         4 requests the financial year 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.

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

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.