# Date Output is incorrect on date formula

Options
✭✭✭✭

=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

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
Options

• ✭✭✭✭✭✭
Options

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

=IFERROR(original_formula, "")

• ✭✭✭✭
Options

I did but then I get this error message:

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

Can you copy/paste what you tried here?

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!