Seeking a fiscal year formula :-)

ChrisA

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!

Paul Newcome

Replace all instances of TODAY() with Date@row

Paul Newcome

Try something like this:

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

ChrisA

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

Paul Newcome

Replace all instances of TODAY() with Date@row

ChrisA

Worked like a charm!

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

Thanks!

Paul Newcome

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.

Paul Newcome

@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.