Formula for Fiscal Year

Heather Druckenmiller

I am trying to create a formula to track Fiscal year by month and Year. I have a column for the Month and a column for the year. The fiscal year date range I am creating is anything between 4/2023 and 3/2024 = FY2024, anything between 4/2022 - 3/2023= FY2023.

KPH

I have two suggestions for you to choose from

Finding the financial year from a date

You can use an IF function to return a year based on whether the date is between the start and end of the financial year, like this:

=IF(AND(Date@row >= DATE(2023, 4, 1), Date@row <= DATE(2024, 3, 31)), "FY2023", 

IF(AND(Date@row >= DATE(2024, 4, 1), Date@row <= DATE(2025, 3, 31)), "FY2024",

IF(AND(Date@row >= DATE(2025, 4, 1), Date@row <= DATE(2026, 3, 31)), "FY2025", 

"")))

Pros - no additional column needed

Cons - you need to add another IF for every year.

Finding the financial year from text columns with month and year

You can also use an IF function to look at the month name and apply logic. If the month name is Jan, Feb or Mar then the financial year is the current year. For all other months it is the current year plus 1. Like this:

=IF(OR(Month@row = "Jan", Month@row = "Feb", Month@row = "Mar"), Year@row, Year@row + 1)

And to get the "FY" to appear you would add

=IF(OR(Month@row = "Jan", Month@row = "Feb", Month@row = "Mar"), "FY" + Year@row, "FY" + (Year@row + 1))

You need to replace "Jan", "Feb", etc with the exact terms you use.

Pros - needs the month and year columns.

Cons - may need extra logic if you don't want it to return anything when the month is blank. The names of the months must be entered consistently.