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

07/24/17 Edited 12/09/19

Hello,

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

 

Olivier

 

 

 

 

 

 

Popular Tags:

Comments

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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.

    Craig

  • Thank you so much Craig. It works fine smiley

This discussion has been closed.