Months Remaining Formula

Options

Trying to come up with a formula to calculate the duration of contract between 2 dates regardless of todays date with a result in months. Then, how do I get the remaining months on the contract from todays date - in months - over multiple years - and if the start date has not began yet.



Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Scotty Bob

    Think these may work for you

    Terms in month -

    =SUM(12 * (YEAR([End date]@row) - YEAR([Start date]@row)), MONTH([End date]@row) - MONTH([Start date]@row))

    Months remaining, just substitute your start date for todays date -

    =SUM(12 * (YEAR([End date]@row) - YEAR(TODAY())), MONTH([End date]@row) - MONTH(TODAY()))

    Hope that helps,

    Thanks

    Paul

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Scotty Bob

    Think these may work for you

    Terms in month -

    =SUM(12 * (YEAR([End date]@row) - YEAR([Start date]@row)), MONTH([End date]@row) - MONTH([Start date]@row))

    Months remaining, just substitute your start date for todays date -

    =SUM(12 * (YEAR([End date]@row) - YEAR(TODAY())), MONTH([End date]@row) - MONTH(TODAY()))

    Hope that helps,

    Thanks

    Paul

  • Scotty Bob
    Options

    Thank you Paul!

  • Scotty Bob
    Options

    I'm using the formula below to calculate the Terms in Months - based on the Start and End dates columns. Based on the results I see - it doesnt look like it is calculating the full months. Almost as if it is one month less.

    =SUM(12 * (YEAR([End Date]@row) - YEAR([Start Date]@row)), MONTH([End Date]@row) - MONTH([Start Date]@row))



  • Genevieve P.
    Options

    Hi @Scotty Bob

    Try thinking of the months simply as numbers to see what you'll calculate:

    June (6) - July (7) = -1 *then adding 12 for each year difference

    December (12) - January (1) = 11 *then adding 12 for each year difference

    June (6) - June (6) = 0 *then adding 12 for each year difference


    If you want to include the end month as an additional month (so June - June = 1, instead of 0) then you can +1 to the end of your formula:

    =SUM(12 * (YEAR([End Date]@row) - YEAR([Start Date]@row)), MONTH([End Date]@row) - MONTH([Start Date]@row) + 1)

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!