Fiscal Year

Monica Gallegos

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

Adriane Price

Thank you for the assistance, this formula ended up working for my needs:

=WEEKNUMBER(Created@row - 35)

Adriane Price

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")))))))))))))

Monica Gallegos

Thanks Adriane let me give this a whirl.

Adriane Price

@Monica Gallegos - did that formula work for you?

David Joyeuse

@Monica Gallegos

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 :)

Adriane Price

@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

David Joyeuse

Hi @Adriane Price

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?

Adriane Price

Thank you for the assistance, this formula ended up working for my needs:

=WEEKNUMBER(Created@row - 35)