Fiscal Year Formula?

ctsammon

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!

TravisK

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

TravisK

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

Thank you!! Worked perfectly!

dmorris

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?