Custom Fiscal Year - help with Oct, Nov and Dec

Options
This discussion was created from comments split from: Bespoke year / fiscal year rather than calendar year?.

Answers

  • Michael Batt
    Options

    @Dan Palenchar or whoever can assist!

    I've been hanging onto the above advice ready to go for it and go through the entire system to convert from calendar to fiscal. Need some help though!

    Formula is returning 2024 instead of 2023, it is May 19th 2024 today.

    1. This Year Start: =IF(MONTH(TODAY()) > 9, DATE(YEAR(TODAY()), 10, 1), DATE(YEAR(TODAY() - 1), 10, 1))

    Returning 01/10/2024 (1st October 2024)

    it should be

    01/10/2023 (1st October 2023)

    For the months of October, Nov and December it would change to 2024 as the next fiscal year starts 1st October.

    I'm a UK user, not sure if there are any differences.

    Regards Michael

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Michael Batt

    For the months that are "greater than 9" (so Oct, Nov, Dec), the DATE portion is returning Today's Year. Instead, you can say "Today's Year minus 1", or last year.

    Try this, reversing the 2 Year statements:

    =IF(MONTH(TODAY()) > 9, DATE(YEAR(TODAY()) -1, 10, 1), DATE(YEAR(TODAY()), 10, 1))

  • Michael Batt
    Options

    Hi @Genevieve P.

    Thank you for coming back to me, its not delivering the desired result. Attached is a jpeg.

    Formula currently returns 01/10/24 Grey arrow.
    (You can see I have manually typed in 01/10/23) above it to check the rest of the formula works.

    The original formula makes sense to me but it returns 2024 not 2023.

    This Year Start: =IF(MONTH(TODAY()) > 9, DATE(YEAR(TODAY()), 10, 1), DATE(YEAR(TODAY() - 1), 10, 1))

    This I think is saying if its

    Oct, Nov or Dec of this year it will return 1st October this year

    Otherwise it return 1st October last year? (The months of Jan to Sept)

    Mike

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Michael Batt

    My apologies, I misunderstood what you were looking to do, this explanation is very helpful.

    You're correct, the current formula should be the one that works.

    Since today's month is 5, it should default to the last statement, which is what you want. However I've found the culprit! We're subtracting 1 from TODAY, not from the YEAR. (So it's getting the Year from yesterday, instead of last Year).

    Change:

    DATE(YEAR(TODAY() - 1), 10, 1))

    To:

    DATE(YEAR(TODAY()) - 1, 10, 1))

    That should do the trick!

    Cheers,
    Genevieve

  • Michael Batt
    Options

    Definitely was a a culprit for sure!

    Thanks for your assistance!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!