Fiscal Year Formula?

Options

I'm looking for a simple formula to generate my fiscal year using the date entered in the "Date of Accident/Incident" column.

Fiscal Year Start: Feb 1st

Fiscal Year End: Jan 31


Outcome desired:

If today's date is 1/20/23 - Display 2022 in FY column

If today's date is 2/01/23 - Display 2023 in FY column


Hope that makes sense and thank you in advance!

Best Answer

  • TravisK
    TravisK ✭✭✭
    Answer ✓
    Options

    Hello,

    Try the following. IncidentDate is a DATE column and Fiscal Year is a TEXT/NUMBER column. The <2 in the formula represents any months prior to February. You could adjust this for a different month if your fiscal year ended in March or June.

    =IF(MONTH(IncidentDate@row) < 2, YEAR(IncidentDate@row) - 1, YEAR(IncidentDate@row))


    In my company, we base our fiscal year on when the year ends. So 1/20/23 would by FY23 and 2/1/23 would be FY24 (because the time range ends on 1/31/24). If that's the case, change the formula to:

    =IF(MONTH(IncidentDate@row) < 2, YEAR(IncidentDate@row), YEAR(IncidentDate@row) + 1)

    I hope that helps!

    Travis

Answers

  • TravisK
    TravisK ✭✭✭
    Answer ✓
    Options

    Hello,

    Try the following. IncidentDate is a DATE column and Fiscal Year is a TEXT/NUMBER column. The <2 in the formula represents any months prior to February. You could adjust this for a different month if your fiscal year ended in March or June.

    =IF(MONTH(IncidentDate@row) < 2, YEAR(IncidentDate@row) - 1, YEAR(IncidentDate@row))


    In my company, we base our fiscal year on when the year ends. So 1/20/23 would by FY23 and 2/1/23 would be FY24 (because the time range ends on 1/31/24). If that's the case, change the formula to:

    =IF(MONTH(IncidentDate@row) < 2, YEAR(IncidentDate@row), YEAR(IncidentDate@row) + 1)

    I hope that helps!

    Travis

  • ctsammon
    Options

    Thank you!! Worked perfectly!

  • dmorris
    Options

    So to extend this, is there a formula I can use that would calculate the fiscal year and quarter? Our fiscal year runs from October 1 through September 30, and I want to have a formula that will look at a date in a cell and calculate the quarter within which that date occurs, so I can filter by quarter. For example, if a cell contains 11/15/23, that is within Q1 of FY24. Any thoughts?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!