Adding different time periods to date

anovak

I'm looking for a function to add different time periods listed in one column (either an amount of week, day, month, or year) to a date in one column and output as a final date in another column.

For example first column says 1 day, second column says 6/1/22, and output of function in third column says 6/2/22. See attached picture for desired outcome. I did it in excel and not sure how to translate it to smartsheets.

Thank you!!

Paul Newcome

I would suggest two helper columns to split out the number and the increment.

=VALUE(LEFT(Term@row, FIND(" ", Term@row) - 1))

=RIGHT(Term@row, LEN(Term@row) - FIND(" ", Term@row))

Then we would have just a straight addition for days, weeks would be the number times 7, and months would require an EDATE formula all dropped into a nested IF. Then years would be added directly to the date itself.

=IF(Increment@row <> "months", DATE(YEAR([Entered Date]@row) + IF(Increment@row = "years", Number@row, 0), MONTH([Entered Date]@row), DAY([Entered Date]@row)) + IF(Increment@row = "days", Number@row, IF(Increment@row = "weeks", Number@row * 7)), IFERROR(DATE(YEAR([Entered Date]@row) + ROUNDDOWN((MONTH([Entered Date]@row) + Number@row) / 12, 0) + IF(IF(MOD(MONTH([Entered Date]@row) + Number@row, 12) = 0, 12, MOD(MONTH([Entered Date]@row) + Number@row, 12)) = 12, -1) - IF(AND(ABS(Number@row) - MONTH([Entered Date]@row) <> 12, Number@row < 0, ABS(Number@row) > MONTH([Entered Date]@row)), 1, 0), IF(MOD(MONTH([Entered Date]@row) + Number@row, 12) = 0, 12, MOD(MONTH([Entered Date]@row) + Number@row, 12)), 1), DATE(IF(MONTH([Entered Date]@row) - ABS(Number@row) < 1, YEAR([Entered Date]@row) - 1, YEAR([Entered Date]@row)), IF(MONTH([Entered Date]@row) - ABS(Number@row) < 1, MONTH([Entered Date]@row) + (12 - ABS(Number@row)), MONTH([Entered Date]@row) - ABS(Number@row)), 1)))