How to add months to date?


I am trying to calculate a clawback expiration date from the date the agreement was signed and the duration of the clawback in months and can't figure out a Smartsheet formula. In Excel the EDATE formula works great. Can someone please help me with this formula in Smartsheet?


  • KPH
    KPH ✭✭✭✭✭✭

    Hi @tblev

    How do you define a month? Is it a calendar month or a 12th of a year?

    If you are using 1/12 of a year you could add 30.4375 days multiplied by the number of months. So April 11, 24 plus 24 months would be plus 24 x 30.4375 days (730.5 days) which would be April 11, 24. However, some expirations might not fall exactly on the same day due to leap years, and short months. For example, 3 months from 1st Feb 2022 would be 3rd May 2022.

    If that is how you figure months you can multiply the number of months by 30.473 and then add it to the date. Both date columns must be date format.

    =[Date Agreement Signed]@row + ([Clawback Duration in Months]@row * 30.4375)

    If you are using calendar months and the expiration date needs to be the same day of the month as the agreement is signed, it is a slightly more complex formula as you will need to add the number of months to the MONTH() part of the date using:

    =MONTH([Date Agreement Signed]@row)+24

    But also handle situations where the month number plus the duration is a number more than 12. If this is something you want to do, can you let us know the maximum duration you will be adding.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!