Savings Calculation by Year Over Multiple Years

Kelly Cepicky
Kelly Cepicky ✭✭
edited 09/19/23 in Formulas and Functions

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?

Answers

  • Ryan Kramer
    Ryan Kramer ✭✭✭✭✭
    edited 09/20/23

    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?

  • Ryan Kramer
    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.
    Genevieve P. Employee Admin

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!