Nested IF AND & OR

Olivier Pillon

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

 

 

 

 

 

 

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.

Craig