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


Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • WinaHath
    WinaHath ✭✭✭✭

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

  • WinaHath
    WinaHath ✭✭✭✭

    @Paul Newcome

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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    =IFERROR(original_formula, "")

  • WinaHath
    WinaHath ✭✭✭✭

    I did but then I get this error message:



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • WinaHath
    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
    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
    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
    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
    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, "")



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!