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

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

Leibel S

@S.Stone

I see what happened here:

the below should fix it

=DATE(YEAR([Date Contract Executed]@row) + ROUNDDOWN(([Months Until Expiration]@row + MONTH([Date Contract Executed]@row)) / 12.00001, 0), ROUNDUP(MOD([Months Until Expiration]@row + MONTH([Date Contract Executed]@row), 12.00001), 0), 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))), "")

S.Stone

So, circling back here, but the first formula had a problem that I couldn't resolve.

I used this formula in the first four rows, but something happened with the Aug 15, '23, date.

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

I used the other formula (below) in the fifth row, and it's resolved!

=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))), "")

Sharing in case anyone else gets this error and is looking for an answer.

Leibel S

@S.Stone

I see what happened here:

the below should fix it

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

S.Stone

@Leibel S

Ah, that works great! Thank you!

Revised formula to the highlighted cell below.

Paul Newcome

@Leibel S I like how you used the 12.0001 in the MOD function. I hadn't thought to do that, and it would save me from having to use the IF statements to say that IF the MOD is zero, output 12, otherwise output the MOD. I am going to have to fiddle with mine a bit and see how much that cuts it down.

Mine is also a little bit overkill for this particular post because it allows for a negative number of months, but copy/paste from something I know works is easier than trying to slim it down. Haha.

Leibel S

@Paul Newcome Thanks. I always try and make it as neat as possible