Bespoke year / fiscal year rather than calendar year?

Michael Batt

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

SoS | Dan Palenchar

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!

SoS | Dan Palenchar

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!

Michael Batt

@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

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!