Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Nested IF AND & OR

Olivier Pillon
Olivier Pillon ✭✭✭
edited 12/09/19 in Archived 2017 Posts


I'm looking for a good formula to collect the number of remaining days to work.

This works fine:

=IF(OR([Assigned to]110 = "User1", [Assigned to]110 = "User2"), [Closing date]110 - TODAY(), "0")

but If I want to add one more condition like at row status it doesn't works:

=IF(AND((OR([Assigned to]110 = "User1", [Assigned to]110 = "User2"), [Closing date]110 - TODAY(), "0"), (Status110 = "Completed", "0", IF(Status110 = "Not Started", "0", IF(Status110 = "Hold", "0"))))

Thanks for your help










  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Try this instead:

    =IF(OR(Status10 = "Hold", Status10 = "Completed", Status10 = "Not Started"), 0, IF(OR([Assigned To]10 = "User 1", [Assigned To]10 = "User 2"), [Closing Date]10 - TODAY(), 0))

    First check for Status, because if any of these states, you don't care who it is assigned to.

    I changed result from "0" to 0 as "0" is text and you may want to use the number later and sometimes text numbers and number numbers may have unusual results.

    Lastly, if you only have one other status (ie "In Progress"), then the formula can be switched to look at one status instead of all the others.


  • Olivier Pillon

    Thank you so much Craig. It works fine smiley

This discussion has been closed.