Seeking a fiscal year formula :-)

I'm looking for a simple formula to generate my fiscal year using the date row.

Fiscal Year Start: Nov 1st

Fiscal Year End: Oct 31

Outcome desired:

If today's date is 8/24/22 - Display FY22 in FY row

If today's date is 11/01/22 - Display FY23 in FY row

Hope that makes sense and thank you in advance!

Find more posts tagged with

Accepted answers

Replace all instances of TODAY() with Date@row

All comments

Try something like this:

="FY" + RIGHT(YEAR(TODAY()) + IF(TODAY()>= DATE(YEAR(TODAY()), 11, 01), 1, 0), 2)

Thanks Paul, that works great for FY21 & FY22.

Hope this paints a better picture:

I tried the new formula but realized the Date@row needs to drive the results.

What would be a good solution to automate the "fiscal year" using the Data@row year over year?

Fiscal Year start: Nov 1st

Replace all instances of TODAY() with Date@row

Worked like a charm!

="FY" + RIGHT(YEAR(Date@row) + IF(Date@row>= DATE(YEAR(Date@row), 11, 01), 1, 0), 2)

Thanks!

Happy to help. 👍️

Deepmala Rajput

https://community.smartsheet.com/discussion/comment/341580#Comment_341580

Hi @ChrisA , I'm trying the same but it's not working, could you please help.

@Deepmala Rajput What do you mean by it is not working? Are you getting an error or an incorrect output?

Deepmala Rajput

Hi @Paul Newcome , My mistake.. It worked absolutely fine. Thanks for your quick response.

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.