Savings Calculation by Year Over Multiple Years

Kelly Cepicky

I need a formula that will calculate/sum savings by year when those savings are dispersed over multiple years.

If monthly savings is $5000/month between 9/19/2023 & 9/18/2024, what is the formula to tell me the 2023 & 2024 savings?

Ryan Kramer

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

Kelly Cepicky

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

Ryan Kramer

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 double-check my formulas and see what I am missing.

Ryan

Genevieve P.

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