Rolling Year Formula


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


Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!