Formula adjustment to calculate date based on years duration

Michaela Kamenska
Michaela Kamenska ✭✭✭✭✭✭
edited 04/19/21 in Formulas and Functions

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:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!