Formula adjustment to calculate date based on years duration

Michaela Kamenska

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!

Paul Newcome

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

Bassam Khalil

Hi @Michaela Kamenska

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

Michaela Kamenska

Hi @Bassam.M Khalil,

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

Bassam Khalil

Hi @Michaela Kamenska

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

Michaela Kamenska

@Paul Newcome can you please help me with your original formula?

Paul Newcome

Do you need years and months, or just years?

Michaela Kamenska

Hi Paul,

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

Paul Newcome

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

Michaela Kamenska

Thanks Paul,

that was way easier than I imagined!

Paul Newcome

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

Tina Chamblee

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?

Paul Newcome

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