My formula doesn't work when I choose the first of the month as Effective Date

WinaHath

this is my formula:

=IFERROR(DATE(YEAR([Effective Date]@row) + ROUNDDOWN((MONTH([Effective Date]@row) + ([Contract Term (Months)]@row)) / 12, 0) + IF(IF(MOD(MONTH([Effective Date]@row) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)) = 12, -1), IF(MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)), DAY([Effective Date]@row) - 1), "")

it works except when I choose the first of the month. Please help.

Nick Korna

Hi @WinaHath,

What result are you expecting if you put the 1st of the month in the effective date?

WinaHath

it is supposed to take the number in the contract term column, add that to the effective date, minus 1 day to output the End Date

Nick Korna

Using your above data as a sample, is this correct (albeit in different date format)?

New formula:

=IFERROR(DATE(YEAR([Effective Date]@row) + ROUNDDOWN((MONTH([Effective Date]@row) + ([Contract Term (Months)]@row)) / 12, 0) + IF(IF(MOD(MONTH([Effective Date]@row) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)) = 12, -1), IF(MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)), MAX(DAY([Effective Date]@row - 1), 1)), "")

The 1st of the month is failing in your formula as you can't have the 0th of the month (from DAY([Effective Date]@row) - 1) ), so the MAX portion of the formula fixes by making any 0s into 1s.

Let me know if I'm barking up the wrong tree with this or if you've any problems/questions.