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
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
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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!