How to add varying months to varying dates?

S.Stone

I am really having trouble getting a formula to work here. I've gone through a number of support articles, but I'm just missing something.

I want a formula that looks at Date Contract Executed and it adds Months Until Expiration to produce a new Date of Expiration. I've got my date columns as dates and the middle column as a number.

Any help is appreciated!

Leibel S

@S.Stone

This should work:

=DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN(([Months Until Expiration]@row + MONTH([Date Contract Executed]@row)) / 12.00001, 0), MOD([Months Until Expiration]@row, 12) + MONTH([Date Contract Executed]@row), DAY([Date Contract Executed]@row))

Leibel S

@S.Stone

This should work:

=DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN(([Months Until Expiration]@row + MONTH([Date Contract Executed]@row)) / 12.00001, 0), MOD([Months Until Expiration]@row, 12) + MONTH([Date Contract Executed]@row), DAY([Date Contract Executed]@row))

S.Stone

I hadn't seen "Rounddown" before — thank you!!

Paul Newcome

This should get the job done for you:

=IFERROR(IFERROR(DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN((MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row) / 12, 0) + IF(IF(MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12) = 0, 12, MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12)) = 12, -1) - IF(AND(ABS([Months Until Expiration]@row) - MONTH([Date Contract Executed]@row) <> 12, [Months Until Expiration]@row < 0, ABS([Months Until Expiration]@row) > MONTH([Date Contract Executed]@row)), 1, 0), IF(MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12) = 0, 12, MOD(MONTH([Date Contract Executed]@row) + [Months Until Expiration]@row, 12)), DAY([Date Contract Executed]@row)), DATE(IF(MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row) < 1, YEAR([Date Contract Executed]@row) - 1, YEAR([Date Contract Executed]@row)), IF(MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row) < 1, MONTH([Date Contract Executed]@row) + (12 - ABS([Months Until Expiration]@row)), MONTH([Date Contract Executed]@row) - ABS([Months Until Expiration]@row)), DAY([Date Contract Executed]@row))), "")