If, Then with a date and N/A

Options

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?

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • north
    north
    edited 04/23/24
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!