Date Output is incorrect on date 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)), "")
why does this output a whole year instead of just 12 months? output should be 12/31/24
Best Answer
-
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
Answers
-
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
-
YOU ARE A GENIUS!!!!! Thank you @Paul Newcome
-
-
@WinaHath You can wrap the entire thing in another IFERROR.
=IFERROR(original_formula, "")
-
I did but then I get this error message:
-
-
=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, "")
-
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.
-
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, "")
-
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, "")
-
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, "")
Help Article Resources
Categories
Check out the Formula Handbook template!