Add month to Date

Options

Hello,

Please help me why when the "C Date" is December, and the "IN Freq" value is "12 Month" I got an error "#INVALID VALUE" and it works if "C Date" is December and the "IN Freq" value is " 6 Month" with the formula below:

=IF([C Date]@row = "", "", IF([IN Freq]@row = "", "", IF((MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1))) > 12, DATE(YEAR([C Date]@row) + 1, MOD(MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1)), 12), DAY([C Date]@row)), DATE(YEAR([C Date]@row), MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1)), DAY([C Date]@row)))))

Thank you!

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Options

    @BonW Whatcha trying to do?

    dm

  • BonW
    BonW ✭✭
    Options

    Hello Dale,

    Thank you for response to my post. I'm trying to automate "Inspection Date". If "IN Freq" value is "6 Month" I like to add 6 months to the "C Date" and populate the date value to the "Inspection Date" and if value "12 Month" I like to add 12 months to the "C Date" and populate the date value to the "Inspection Date". For some reason when the "In Freq" value is "12 Month" and the "C Date" month is December the system displayed the "#INVALID VALUE" other months are working and not December month.

    =IF([C Date]@row = "", "", IF([IN Freq]@row = "", "", IF((MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1))) > 12, DATE(YEAR([C Date]@row) + 1, MOD(MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1)), 12), DAY([C Date]@row)), DATE(YEAR([C Date]@row), MONTH([C Date]@row) + VALUE(LEFT([IN Freq]@row, FIND(" ", [IN Freq]@row) - 1)), DAY([C Date]@row)))))

    Thank you!