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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!