Rolling Year Formula

Cesar Perez

I have a file that will be used to calculate 13 rolling periods for graphs and reports. The file contains 13 rows only for the number of months needed in the reports. I need a formula that will enter the year number in the "RollingYear" column. I have the formulas for the needed month number. The start calculating from the bottom up. In other words row 13 is the current month -1 month. The year formula should be based on the row column. In other words row 13 should be the year for the month shown in "RollingMonth" column. Row 13 shows month 12 so the year should be 2022. There are two Decembers shown in the example. Row 13 should be 2022 and row 1 should be 2021. I have tried different formulas but I don't get the desired result.

Any help with this formula will be appreciated. Thank you

https://app.smartsheet.com/b/publish?EQBCT=bb95c12d281641a891216e72c0677e50

Jeff Reisman

@Cesar Perez I've done rolling dates before, but usually across columns instead of up rows. However I think I figured this out.

In RollingYear13, use the following to get the correct year:

=IF(MONTH(TODAY()) - 1 = 0, YEAR(TODAY()) - 1, YEAR(TODAY()))

Then use this in the rest of the RollingYear rows:

=IF(RollingMonth$13 - RollingMonth@row <= 0, (RollingYear$13 - 1), RollingYear$13)

Jeff Reisman

@Cesar Perez I've done rolling dates before, but usually across columns instead of up rows. However I think I figured this out.

In RollingYear13, use the following to get the correct year:

=IF(MONTH(TODAY()) - 1 = 0, YEAR(TODAY()) - 1, YEAR(TODAY()))

Then use this in the rest of the RollingYear rows:

=IF(RollingMonth$13 - RollingMonth@row <= 0, (RollingYear$13 - 1), RollingYear$13)

Cesar Perez

Hi @Jeff Reisman, thank you for the quick reply. I will try your formula.