Why
Whether you are preparing a forecast or historical data, when your financial year does not align with the calendar year, you will have to make some adjustments to ensure that you can get all the data such as the year number. It does not have to financial year, but if your period start month does not align with the calendar year, you will have this problem.
What is it
Use cases
- Where dates are calculated on a monthly basis from a static date
Formula
=year(eomonth(C$1,-month($B$1)))-year($B$1)+1
Example
In the example below, the forecast starts from March as February is the first month. To get the year number, you cannot simply use the YEAR() formula as it will result in 2 in month 11 which is January 2025.
Logic
The logic and approach behind this formula is to convert the months to the calendar months. 1 February 2024 is a fixed start date. We know that the February is the second (2) month in the Calendar. Therefore, we find the month for current column (the month in which we are calculating). For example, if we are calculating it for C4, we will check the month of C1. Then we will take away the month number for B1 which is a fixed date.
Video 🎦
If this has helped you,
Buy me a coffee
Patreon
Newsletter