# Formula adjustment to calculate date based on years duration

Hello,

I currently use the below formula to automatically give me a date if I enter the duration. Now, this currently only works if I enter it in months. Can someone help me adjust this so that it calculates based on years duration?

=IFERROR(DATE(YEAR([Start Date]@row) + ROUNDDOWN((MONTH([Start Date]@row) + [Duration (months)]@row) / 12, 0) + IF(IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)) = 12, -1), IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)), DAY([Start Date]@row)) - 1, 0)

Thanks!

Tags:

• If it is just years, you would use something along the lines of...

=DATE(YEAR([Start Date]@row) + [Duration (years)]@row, MONTH([{Start Date]@row), DAY([Start Date]@row))

• Hope you are fine, you did a brilliant formula but you can do it in an easy way by converting the duration whatsoever it to days then use Start date + Duration ( Days ) to get the end date.

End Date =([Duration (Years)]@row * 365 + [Duration (Months)]@row * 30.4) + [Start Date]@row

Check the following screenshot

PMP Certified

[email protected]

www.mobilproject.it

• Thank you for your suggestion. I would like to stick to a single duration column in this case, rather than having a column for years AND months. Can you help with the original formula?

Also, @Bassam.M Khalil , thank you, but I need to give credit where credit is due and it's formula by @Paul Newcome :)

• Yes, it's a good idea, I think Paul will help you more than me because he is the creator of this brilliant formula.

PMP Certified

[email protected]

www.mobilproject.it

• Do you need years and months, or just years?

• Hi Paul,

It would be exactly the same layout as below, except for the duration (months) it will be Duration (years).

• If it is just years, you would use something along the lines of...

=DATE(YEAR([Start Date]@row) + [Duration (years)]@row, MONTH([{Start Date]@row), DAY([Start Date]@row))

• Thanks Paul,

that was way easier than I imagined!

• Haha. We got lucky on that one. Happy to help. 👍️

• I have set up the easiest way to do this due date, but I need the day to remain the same as the original state date. This is probably changing due to a leap year. How do I add that to the date. I have a start date as 1/19/21 and the end date is five years from the start date and should be 1/19/26. How do I add the leap year information?

• @Tina Chamblee You shouldn't need to change anything from the above formula. The above formula pulls the MONTH() and DAY() from the original date and should only be adjusting the year.

Exactly what is the formula you are using?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!