My organization uses Excel 2016 but plan to remove the ability to use VBA macros or user defined formula soon. We are trying to find ways to adjust to the change.
Question:
Can we replace the following user defined formulas like the following with something else?
user defined formula: FYFinder
FYFinder takes in a cell reference to a date (or attempts to convert a numerical reference to an Excel date) and provides the fiscal year of the date. The new fiscal year starts on October 1st of each year. So for 2025, all days from January 1st 2025 to Sept 30th 2025 are in fiscal year 2025, and all days after October 1 2025 to dec 31 2025 are in fiscal year 2026. The time part of datetime values are ignored and non numerical inputs produce an error
For example, given the following inputs, the outputs are:
Note that A4 demonstrates the function treats all numbers as dates, and that non numbers produce errors
This function can be easily replicated with the formulas:
=IF(MONTH(A1)>=10,1+YEAR(A1),YEAR(A1))
Or a concise version
=--(MONTH(A1)>=10)+YEAR(A1)
So given that information, what can we do to replicate the functionality without VBA? I've tried using a custom named range but the examples I see can't reference specific cells. For example:
=--(MONTH(TODAY())>=10)+YEAR(TODAY())
which only works for today. Or =--(MONTH(A1)>=10)+YEAR(A1) which only references cell A1
(My organization plans to upgrade to Excel 365 soon, so if an answer comes from that version then it can help us to adopt it faster.
Recent versions of Excel have had something called a LAMBDA function (introduced in December 2020). These can also be set up as Defined Names (from the "Formula" tab in the Ribbon)
As such, your UDF can be replaced by a Defined Name, also called FYFinder, with the following formula in the "Refers To" field:
=LAMBDA(serial_number, --(MONTH(serial_number)>=10)+YEAR(serial_number))
(The variable name serial_number was chosen to match existing Date functions, suchas WEEKDAY, DAY, YEAR, etc)
In the meantime, you can also use a Relative Reference.
Suppose that your Worksheet is called "Sheet1". If you select the cell B1 (N.B. that step is very important), and create a new Defined Name called FYRelative, then you can give it the following "Refers To":
=Year(Sheet1!A1)+(Month(Sheet1!A1)>10)
Rather than being used as a formula, you use this as a constant, e.g.
="The Year is " & FYRelative
This will always return the FYFinder value for the cell immediately to the left, making it significantly less useful in the long term.