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!

Find more posts tagged with

Accepted answers

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

All comments

TravisK

Hello,

=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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.