Formula adjustment to calculate date based on years duration

Options
✭✭✭✭✭✭
edited 04/19/21

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:

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
edited 04/19/21
Options

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

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
edited 04/19/21
Options

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 :)

• ✭✭✭✭✭✭
Options

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

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

• ✭✭✭✭✭✭
Options

Do you need years and months, or just years?

• ✭✭✭✭✭✭
Options

Hi Paul,

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

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

Thanks Paul,

that was way easier than I imagined!

• ✭✭✭✭✭✭
Options

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

• Options

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?

• ✭✭✭✭✭✭
Options

@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!