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
Check out the Formula Handbook template!