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!

• ✭✭✭✭✭✭
Options

@BonW Whatcha trying to do?

dm

• ✭✭
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!