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 :)

Find more posts tagged with

Accepted answers

Snaillybob

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

All comments

che.rabajante

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

Snaillybob

Yes it is che

Snaillybob

..and will try and sort out a screenshot:)

Snaillybob

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.