Does NOT contain formula

Question

I am trying to create a formula that looks at 2 different columns Term Ending Date and Amendment - Ending Date.

If column Amendment Date does NOT contain N/A it should return the value in [Amendment - Ending Date] @row. This part of the formula works I can't get the third party to work.

The third part should say if it DOES contain N/A then return value in [TERM - Ending Date] @row.

With the formula below I get #INVALID COLUMN VALUE error message.

Help?

=IF(NOT(CONTAINS("N/A", [Amendment - Ending Date]@row)), [Amendment - Ending Date]@row, [TERM - Ending Date]@row)

Brian Wilson DC · Accepted Answer

@n7teixeira Make sure all of your date columns are setup identically. In this case, since you are using a non-date entry in two of them, make sure all three have this following option toggled off:

Below you will see that my table is producing the results that you are looking for.  To do so please use the following formula:

=IF([Amendment Ending Date]@row = "N/A", [Term Ending Date]@row, IF([Amendment Ending Date]@row <> "N/A", [Amendment Ending Date]@row))

I hope this helps!

Regards,

Brian