Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Custom Fiscal Year - help with Oct, Nov and Dec

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭

      @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

      · Share on FacebookShare on Twitter
    • Employee

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

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Hi @Genevieve P.

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

      Capture.JPG

      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

      · Share on FacebookShare on Twitter
    • Employee

      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

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Definitely was a a culprit for sure!

      Thanks for your assistance!

      · Share on FacebookShare on Twitter
    • Community Champion

      @Michael Batt - just saw this! All set? Looks like @Genevieve P. came to the resuce?

      School of Sheets (Smartsheet Partner)

      If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

      · Share on FacebookShare on Twitter
    • ✭✭✭

      @Dan Palenchar All set thank you! Now operating on fiscal year on many sheet thanks to @Genevieve P.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions