Formula for Fiscal Year

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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!