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

@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)
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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!
Answers

@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)
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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!

Hi @Jeff Reisman, thank you for the quick reply. I will try your formula.
Help Article Resources
Categories
Check out the Formula Handbook template!