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
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
Comments
-
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
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives