A lambda to encapsulate the calculation of the Financial Half-end Date for a given date.

Development Rationale

The purpose in developing this lambda is to simplify the derivation of the financial half-end date for a given date in a model, such as a transaction date or other critical action 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 half-end 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.

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,
2  LET(fyEnd,λFinancialYearEnd(as_at_date,fy_end_month),
3  fyHend,EOMONTH(fyEnd,-6),
4  IF(as_at_date<=fyHend,fyHend,fyEnd)))

In line 1 above, the lambda is defined, with two arguments: as_at_date and fy_end_month.  fy_end_month is expected to be a number in the range 1..12,

In line 2, the LET function in the third 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 fyHend calculates the date 6 months before the fyEnd date.

Finally, in line 4, the result returned by the LET function is calculated, which is the result of the LAMBDA: it tests whether the as_at_date is lest than or equal to the fyHend, in which case that is returned, otherwise fyEnd is returned.

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

=λFinancialHalfEnd(as_at_date,fy_end_month)

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 year-ends 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

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.