Return "Yes" or "No" in IF statement with multiple criteria - check if date is past due
This one is proving difficult for me - trying to create a helper column that does the following
'IF (deadline cell) isn't BLANK, AND (status cell) is one of "Active" or "Closed", AND (deadline cell) IS in the past return 'Yes" (past due), otherwise "No"
Would love there to be a way of doing this as a summary sheet field (i.e count how many rows have deadlines that are in the past) but that may be a bridge too far!!
Any help gratefully received :)
Best Answer
-
Finally sorted it with this formula
=IF(Deadline@row <> "", IF(Status@row <> "", IF(Status@row <> "Closed", IF(AND(Deadline@row < TODAY(), Status@row <> "Closed"), "Yes", "No"))))
Decided that reporting if closed statuses were closed after their deadline passed was unnecessary
Answers
-
Hello @Snaillybob
May know if "deadline cell" is in date column type? If possible to send a screenshot of the sheet your working on, so we can figure out. Thanks
che
-
Yes it is che
-
..and will try and sort out a screenshot:)
-
Finally sorted it with this formula
=IF(Deadline@row <> "", IF(Status@row <> "", IF(Status@row <> "Closed", IF(AND(Deadline@row < TODAY(), Status@row <> "Closed"), "Yes", "No"))))
Decided that reporting if closed statuses were closed after their deadline passed was unnecessary
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!