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

Development Rationale

The purpose in developing this lambda is to simplify the derivation of the financial half year 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 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 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 halves may may be just shown as 2024 1H or 2024 2H.  It may have a prefix, such as FY 2024 1H or FY 2024 2H.  And it may be shown as 2023/24 xH or 23/24 xH.  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.

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,λFinancialYear(as_at_date,fy_end_month,fy_format),
4   fyHalf,2-(as_at_date<=EOMONTH(fyEnd,-6)),
5   fyResult&" "&fyHalf&"H"))

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 fyHalf is calculated by using EOMONTH to calculate the half-year-end date at the end of the month 6 months before fyEnd and then returning 1 if the date is earlier than that, otherwise returns 2.

Finally, in line 5, the result returned by the LET function is calculated, which is the result of the LAMBDA: It is the value of fyResult with a 1H or 2H appended, based on whether the as_at_date falls in the first or second half of the year.

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

=λFinancialHalf(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 4, indicating the format of the value returned.
         1 requests the financial half year be returned as a text string made up of the year and half, for example "2024 1H"
         2 requests the financial half year be returned as a text string, prefixed with "FY" with the half appended, for example "FY 2024 2H"
         3 requests the financial half year be returned as a text string showing the beginning and ending calendar years with the half appended, for example "2023/24 1H".  Note that for December year-ends, the result is returned as for fy_Format = 1
         4 requests the financial half 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. 2H"

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.