Bespoke year / fiscal year rather than calendar year?

Hi Everyone,

We have a system of many sheets which reports various metrics of this year and the previous two years. The idea being we are constantly seeing three years metrics for comparison on dashboards and reports with no intervention. Directors have asked if we could instead show metrics for the company year which runs beginning of October to the end of September.

Formulas I use generate a Yes or No in three columns, I then count the Yes entries to generate the metrics for three years.

This year =IF(YEAR([Customer reported date]@row) = YEAR(TODAY(0)), "YES", "NO")

Year before =IF(YEAR([Customer reported date]@row) = YEAR(TODAY(0)) - 1, "YES", "NO")

Two years before =IF(YEAR([Customer reported date]@row) = YEAR(TODAY(0)) - 2, "YES", "NO")

I can only come up with manual methods based on dates that requires intervention, any ideas how to do this with no intervention?

Michael

Best Answer

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    Hello @Michael Batt !

    I'm understanding this to mean October 1st of x year is the beginning date of your Fiscal Year and September 30 of x+1 year is the last day, for example 10/1/22 - 9/30/23 is last year and 10/1/23 was the start of this year which ends 9/30/24.

    If that's correct, you can make some adjustments so this will work automatically and adjust each year and it looks like this:












    Determining Start and End Dates for Fiscal Years

    First, in the Sheet Summary section I made fields to automatically determine the Start and End Dates for This Year and Last Year (I did not do 2 years ago in the interest of time but it would work very similarly and I wrote the formulas in).

    Here are the formulas:

    1. This Year Start: =IF(MONTH(TODAY()) > 9, DATE(YEAR(TODAY()), 10, 1), DATE(YEAR(TODAY() - 1), 10, 1))
    2. This Year End: =DATE(YEAR([This Year Start]#) + 1, 9, 30)
    3. Last Year Start: =DATE(YEAR([This Year Start]#) - 1, 10, 1)
    4. Last Year End: =DATE(YEAR([This Year End]#) - 1, 9, 30)
    5. -2 Years Start: =DATE(YEAR([This Year Start]#) - 2, 10, 1)
    6. -2 Years End: =DATE(YEAR([This Year End]#) - 2, 9, 30)

    Determining if a row is in a given Fiscal Year

    Then, you would use these formulas in the This Year and Last Year Columns (I am using checkboxes but you could have the formula return Yes rather than 1 which is the value for a checked box).

    1. This Year: =IF(AND([Customer Reported Date]@row >= [This Year Start]#, [Customer Reported Date]@row < [This Year End]#), 1)
    2. Last Year: =IF(AND([Customer Reported Date]@row >= [Last Year Start]#, [Customer Reported Date]@row < [Last Year End]#), 1)
    3. -2 Years: =IF(AND([Customer Reported Date]@row >= [-2 Years Start]#, [Customer Reported Date]@row < [-2 Years End]#), 1)

    I hope that helps!

    _____________________________________________________________________________________________

    👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

    🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

    ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

    PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    Hello @Michael Batt !

    I'm understanding this to mean October 1st of x year is the beginning date of your Fiscal Year and September 30 of x+1 year is the last day, for example 10/1/22 - 9/30/23 is last year and 10/1/23 was the start of this year which ends 9/30/24.

    If that's correct, you can make some adjustments so this will work automatically and adjust each year and it looks like this:












    Determining Start and End Dates for Fiscal Years

    First, in the Sheet Summary section I made fields to automatically determine the Start and End Dates for This Year and Last Year (I did not do 2 years ago in the interest of time but it would work very similarly and I wrote the formulas in).

    Here are the formulas:

    1. This Year Start: =IF(MONTH(TODAY()) > 9, DATE(YEAR(TODAY()), 10, 1), DATE(YEAR(TODAY() - 1), 10, 1))
    2. This Year End: =DATE(YEAR([This Year Start]#) + 1, 9, 30)
    3. Last Year Start: =DATE(YEAR([This Year Start]#) - 1, 10, 1)
    4. Last Year End: =DATE(YEAR([This Year End]#) - 1, 9, 30)
    5. -2 Years Start: =DATE(YEAR([This Year Start]#) - 2, 10, 1)
    6. -2 Years End: =DATE(YEAR([This Year End]#) - 2, 9, 30)

    Determining if a row is in a given Fiscal Year

    Then, you would use these formulas in the This Year and Last Year Columns (I am using checkboxes but you could have the formula return Yes rather than 1 which is the value for a checked box).

    1. This Year: =IF(AND([Customer Reported Date]@row >= [This Year Start]#, [Customer Reported Date]@row < [This Year End]#), 1)
    2. Last Year: =IF(AND([Customer Reported Date]@row >= [Last Year Start]#, [Customer Reported Date]@row < [Last Year End]#), 1)
    3. -2 Years: =IF(AND([Customer Reported Date]@row >= [-2 Years Start]#, [Customer Reported Date]@row < [-2 Years End]#), 1)

    I hope that helps!

    _____________________________________________________________________________________________

    👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

    🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

    ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

    PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

  • @Dan Palenchar 

    Thank you, I can see the method now.

    Although I'm going to try having the intended summary fields on my central control sheet. Then have all my sheets connect to this one for the intended year start & end dates.

    I don't foresee any issues with this but please do let me know if I should for some reason use the summary fields on each sheet.

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hello @Michael Batt -

    Ah I see, you will have multiple Sheets using these dates. In that case, I would recommend to either:

    1. Make a Sheet specifically for having the dates you want in the main columns rather than the Sheet Summary reference this Date Sheet as needed, OR
    2. Make a template of the version with the Sheet Summary fields and use that as you need.

    _____________________________________________________________________________________________

    👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

    🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

    ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

    PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!

    PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!