If, Then with a date and N/A

north

Hello, I'm attempting to put 3-4 arguments together. I can get 3 to work, but not the 4th. I have 3 columns: Letter Due, Letter Sent, and Sent in Window. The Yes, No, N/A are calculated for Sent in Window using the below formula

=IF(AND(ISDATE([*Confirmation Letter Due*]@row), ISDATE([*Confirmation Letter Sent*]@row), [*Confirmation Letter Sent*]@row <= [*Confirmation Letter Due*]@row), "Yes", IF(AND(ISDATE([*Confirmation Letter Due*]@row), ISDATE([*Confirmation Letter Sent*]@row), [*Confirmation Letter Sent*]@row > [*Confirmation Letter Due*]@row), "No", IF([*Confirmation Letter Due*]@row = "N/A", "N/A")))

The issue arises when Letter Due is N/A, but a date is entered in Letter sent, then I get #INVALID OPERATION for Sent in Window and I would like this to =N/A. Is this possible?

bisaacs

Hey @north,

I just tried your formula and it looks like it'll only process the 4th argument if "Confirmation Letter Sent" is blank. We just have to reorder the functions arguments and it seems to be working:

=IF(OR([Letter Due]@row = "N/A", [Letter Sent]@row = "N/A"), "N/A", IF(AND(ISDATE([Letter Due]@row), ISDATE([Letter Sent]@row), [Letter Sent]@row <= [Letter Due]@row), "Yes", IF(AND(ISDATE([Letter Due]@row), ISDATE([Letter Sent]@row), [Letter Sent]@row > [Letter Due]@row), "No")))

Hope this helps!

bisaacs

Hey @north,

I just tried your formula and it looks like it'll only process the 4th argument if "Confirmation Letter Sent" is blank. We just have to reorder the functions arguments and it seems to be working:

=IF(OR([Letter Due]@row = "N/A", [Letter Sent]@row = "N/A"), "N/A", IF(AND(ISDATE([Letter Due]@row), ISDATE([Letter Sent]@row), [Letter Sent]@row <= [Letter Due]@row), "Yes", IF(AND(ISDATE([Letter Due]@row), ISDATE([Letter Sent]@row), [Letter Sent]@row > [Letter Due]@row), "No")))

Hope this helps!

north

Thank you!