Date Output is incorrect on date formula

WinaHath

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

why does this output a whole year instead of just 12 months? output should be 12/31/24

Paul Newcome

Give this a try:

=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) + [Contract Term (Months)]@row, 12)) = 12, -1) - IF(AND(ABS([Contract Term (Months)]@row) - MONTH([Effective Date]@row) <> 12, [Contract Term (Months)]@row < 0, ABS([Contract Term (Months)]@row) > MONTH([Effective Date]@row)), 1, 0), IF(MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12) = 0, 12, MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12)), 1), DATE(IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, YEAR([Effective Date]@row) - 1, YEAR([Effective Date]@row)), IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, MONTH([Effective Date]@row) + (12 - ABS([Contract Term (Months)]@row)), MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row)), DAY([Effective Date]@row))) - 1

Paul Newcome

Give this a try:

=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) + [Contract Term (Months)]@row, 12)) = 12, -1) - IF(AND(ABS([Contract Term (Months)]@row) - MONTH([Effective Date]@row) <> 12, [Contract Term (Months)]@row < 0, ABS([Contract Term (Months)]@row) > MONTH([Effective Date]@row)), 1, 0), IF(MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12) = 0, 12, MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12)), 1), DATE(IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, YEAR([Effective Date]@row) - 1, YEAR([Effective Date]@row)), IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, MONTH([Effective Date]@row) + (12 - ABS([Contract Term (Months)]@row)), MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row)), DAY([Effective Date]@row))) - 1

WinaHath

YOU ARE A GENIUS!!!!! Thank you @Paul Newcome

Paul Newcome

Happy to help. 👍️

WinaHath

@Paul Newcome

I get an invalid data when the date column is blank. please help!

Paul Newcome

@WinaHath You can wrap the entire thing in another IFERROR.

=IFERROR(original_formula, "")

WinaHath

I did but then I get this error message:

WinaHath

Paul Newcome

Can you copy/paste what you tried here?

WinaHath

=IFERROR(DATE(YEAR([Effective Date]@row) + ROUND((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) + [Contract Term (Months)]@row, 12)) = 12, -1) - IF(AND(ABS([Contract Term (Months)]@row) - MONTH([Effective Date]@row) <> 12, [Contract Term (Months)]@row < 0, ABS([Contract Term (Months)]@row) > MONTH([Effective Date]@row)), 1, 0), IF(MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12) = 0, 12, MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12)), 1), DATE(IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, YEAR([Effective Date]@row) - 1, YEAR([Effective Date]@row)), IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, MONTH([Effective Date]@row) + (12 - ABS([Contract Term (Months)]@row)), MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row)), DAY([Effective Date]@row))) - 1, "")

WinaHath

Also, not all contracts start on the first day of the month and I noticed that it rounds off the dates. is it because I have ROUNDDOWN on the formula? I need the dates to be accurate and not rounded off. on the Screenshot below the record that has a contract term of 24 month should have an end date of 6/20/24.

Paul Newcome

Slight tweak to the middle of the formula to fix the rounding issue (nothing to do with the ROUNDDOWN function actually) and the syntax for the extra IFERROR. You needed to add another IFERROR statement to the beginning.

=IFERROR(IFERROR(DATE(YEAR([Effective Date]@row) + ROUND((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) + [Contract Term (Months)]@row, 12)) = 12, -1) - IF(AND(ABS([Contract Term (Months)]@row) - MONTH([Effective Date]@row) <> 12, [Contract Term (Months)]@row < 0, ABS([Contract Term (Months)]@row) > MONTH([Effective Date]@row)), 1, 0), IF(MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12) = 0, 12, MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12)), DAY([Effective Date]@row)), DATE(IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, YEAR([Effective Date]@row) - 1, YEAR([Effective Date]@row)), IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, MONTH([Effective Date]@row) + (12 - ABS([Contract Term (Months)]@row)), MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row)), DAY([Effective Date]@row))) - 1, "")

WinaHath

good morning @Paul Newcome

why does this happen? End date should be 5/31/29

I copied and pasted the above corrected formula you sent me.

=IFERROR(IFERROR(DATE(YEAR([Effective Date]@row) + ROUND((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) + [Contract Term (Months)]@row, 12)) = 12, -1) - IF(AND(ABS([Contract Term (Months)]@row) - MONTH([Effective Date]@row) <> 12, [Contract Term (Months)]@row < 0, ABS([Contract Term (Months)]@row) > MONTH([Effective Date]@row)), 1, 0), IF(MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12) = 0, 12, MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12)), DAY([Effective Date]@row)), DATE(IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, YEAR([Effective Date]@row) - 1, YEAR([Effective Date]@row)), IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, MONTH([Effective Date]@row) + (12 - ABS([Contract Term (Months)]@row)), MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row)), DAY([Effective Date]@row))) - 1, "")

Paul Newcome

It should be, and it is in my test sheet. Here it is pulled straight from my sheet again.

=IFERROR(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) + [Contract Term (Months)]@row, 12)) = 12, -1) - IF(AND(ABS([Contract Term (Months)]@row) - MONTH([Effective Date]@row) <> 12, [Contract Term (Months)]@row < 0, ABS([Contract Term (Months)]@row) > MONTH([Effective Date]@row)), 1, 0), IF(MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12) = 0, 12, MOD(MONTH([Effective Date]@row) + [Contract Term (Months)]@row, 12)), DAY([Effective Date]@row)), DATE(IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, YEAR([Effective Date]@row) - 1, YEAR([Effective Date]@row)), IF(MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row) < 1, MONTH([Effective Date]@row) + (12 - ABS([Contract Term (Months)]@row)), MONTH([Effective Date]@row) - ABS([Contract Term (Months)]@row)), DAY([Effective Date]@row))) - 1, "")