How to Set Up Financial Year Dates That Don’t Follow the Calendar Year?

This discussion was created from comments split from: Formula that will update the new Year dates.

Answers

  • A_C
    A_C ✭✭✭

    Hi Brett,

    Will this work for financial years that are not calendar years?

    We have:

    Start 01/02/25

    End 31/01/26

    I've used

    Start =DATE(YEAR(TODAY()), 2, 1)

    End =DATE(YEAR(TODAY()) + 1, 1, 31) (Just added a year to the current year)

    I'm thinking that this will not work for our fin year, I've tested it with:

    Date Test Col

    Test - Date Today

    01/01/25

    Financial Year Start

    Feb 1, 2025

    Financial Year End

    Jan 31, 2026

    Financial Year Start =DATE(YEAR([Date Test]5), 2, 1)

    Financial Year End =DATE((YEAR([Date Test]5) + 1), 1, 31)

    Cheers in advance

  • Paul Newcome
    Paul Newcome Community Champion
  • A_C
    A_C ✭✭✭

    @Paul Newcome

    Hi Paul,

    I've got an expenses system for approx 30 people.

    One of the components is the Expenses Configuration sheet. Sounds a bit grand but it is basically used for, holding / linking departments, managers and approvers etc.

    I want to add the Fin Start and Fin End dates to this sheet, so I can link them to the individual expense trackers for each member of staff, so it updates all the sheets, and makes it easier to setup for new starters. This will then be used for calcs, relating to this year / last year etc. I want the financial year to update each year automatically.

    Cheers,

    A

  • Paul Newcome
    Paul Newcome Community Champion

    Ok. That's high level, but what are you hoping to accomplish exactly with the formula(s)? It looks like you are trying to manipulate a date based on today's date, but how exactly?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!