Fiscal Year
Our organization works in a Fiscal Year Model (Q1AprMayJun Q2JulAugSep Q3OctNovDec Q4JanFebMar). I have a metrics sheet with a few YTD and Year Over Year calculations that leverage the YEAR parameter. Is there a simple replace that's recommended so that the formulas look for any particular year using the fiscal year?
I'm hoping to avoid having to update a formula once the fiscal year ends. Any suggestions are appreciated.
Thanks, Monica
Best Answer
-
Thank you for the assistance, this formula ended up working for my needs:
=WEEKNUMBER(Created@row - 35)
Adriane
Answers
-
okay I am definitely no guru, however, we put this together. Would this work for you?
I am not sure how to get it to convert for the column formula though (messed with that a few times and kept getting "unparseable")
=IF(ISDATE(ColumnName), IF(YEAR(ColumnName) = 2020, "FY21", IF(YEAR(ColumnName) = 2021, "FY22", IF(YEAR(ColumnName) = 2022, "FY23", IF(YEAR(ColumnName) = 2023, "FY24", IF(YEAR(ColumnName) = 2024, "FY25", IF(YEAR(ColumnName) = 2025, "FY26", IF(YEAR(ColumnName) = 2026, "FY27", IF(YEAR(ColumnName) = 2027, "FY28", IF(YEAR(ColumnName) = 2028, "FY29", IF(YEAR(ColumnName) = 2029, "FY30", IF(YEAR(ColumnName) = 2030, "FY31", IF(YEAR(ColumnName) = 2031, "FY32")))))))))))))
Adriane
-
Thanks Adriane let me give this a whirl.
-
-
Let's try to shorten this formula from @Adriane Price
It's working fine, but it's a tad long :P
=IF(ISDATE([ColumnName]@row), "FY" + (VALUE(RIGHT(YEAR([ColumnName]@row),2))+1),"")
This will do it for many years to come :)
-
@David Joyeuse - thank you yes it did work for a bit until the start of this year, we are in week 49 or our 2021 Fiscal year and it is showing FY22
=IF(ISDATE(Created@row), "FY" + (VALUE(RIGHT(YEAR(Created@row), 2)) + 1), "")
Should I just go back to my marathon formula?
Thank you
Adriane
-
Why is it that your month column display 1 which is supposedly January and Work week being 49?
Could you show what's in your "created" column please?
-
Thank you for the assistance, this formula ended up working for my needs:
=WEEKNUMBER(Created@row - 35)
Adriane
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 214 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 456 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives