Using the OR and AND Function together
I have a checkbox that I need to have checked if the Due Date Field is less than today and the Status does not equal Completed or No Longer Required. I've tried multiple combinations of formulas and I have not been able to figure it out. I need some help. Here is the formula that I currently have.
=IF(OR(AND([Due Date]15 < TODAY(), AND(NOT(Status15 = "Completed", Status15 = "No Longer Required")))), 1, 0)
Thanks,
Robert
Answers
-
@rpyonker The OR and the first AND basically cancel each other out.
You need to change to this:
=IF(AND([Due Date]@row < TODAY(), OR(NOT(Status@row = "Completed", Status@row = "No Longer Required"))), 1, 0)
Also note the @row optimization for your formula. https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
-
Thank you Kelly for your answer. I put in the formula as you suggested but it didn't work. I did finally modify it by adding OR(NOT( for each of the options of Completed or No Longer Required and that worked. I did however try to add a third one but then I got an unparsable error. Is there a limit on how may times you can use OR(NOT( in a formula?
=IF(AND([Due Date]@row < TODAY(), OR(NOT(Status@row = "Completed", OR(NOT(Status@row = "No Longer Required"))))), 1, 0)
Also, thank you for the time on optimizing the formula with the @row reference.
Thank you for you help.
-
The second OR is the problem in your new formula and the NOT segments weren't closed in the right spaces
=IF(AND([Due Date]@row < TODAY(), OR(NOT(Status@row = "Completed"), NOT(Status@row = "No Longer Required"){*Add a comma and then any additional conditions of the OR here, otherwise delete everything between these curly braces*})), 1, 0)
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| business optimization product manager
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!