Savings Calculation by Year Over Multiple Years
Answers

Step 1:
You'll need to have columns for:
Start Date
End Date
Monthly Savings
Input the corresponding values in these columns.
Step 2:
To compute savings for 2023:
=IF(AND(YEAR([Start Date]@row) = 2023, YEAR([End Date]@row) = 2023), MONTH([End Date]@row)  MONTH([Start Date]@row) + 1, IF(YEAR([Start Date]@row) = 2023, 12  MONTH([Start Date]@row) + 1, 0)) * [Monthly Savings]@row
What this does:
 It first checks if both the start and end dates are in 2023. If so, it calculates the number of months between the two dates and multiplies it by the monthly savings.
 If the start date is in 2023 but the end date is not, it calculates the number of months from the start date to the end of 2023.
 If neither of the above conditions is met, it returns 0.
Step 3:
To compute savings for 2024:
=IF(AND(YEAR([Start Date]@row) = 2024, YEAR([End Date]@row) = 2024), MONTH([End Date]@row)  MONTH([Start Date]@row) + 1, IF(YEAR([End Date]@row) = 2024, MONTH([End Date]@row), 0)) * [Monthly Savings]@row
Hope that helps!
Ryan

Thanks Ryan but that didn't work. Could you check your formulas to make sure nothing is missing or wrong?

Darn!
That should be roughly how to approach it and solve it though. I normally pull this stuff out personally and compute it using Python and then push it back into Smartsheet. I find it to be a lot easier than this method.
Maybe someone here can doublecheck my formulas and see what I am missing.
Ryan

Hey @Kelly Cepicky
When you say it doesn't work, can you clarify what that means? Are you getting an incorrect result or an error?
It would be helpful to see a screen capture of your sheet (including column names) with the formula open in a cell, but please block out sensitive data.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!