Fiscal Year Formula?
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

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

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

Thank you!! Worked perfectly!

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
Categories
Check out the Formula Handbook template!