IF/AND formula based on a drop down column if the cell is blank

Krista Cornew

Hi All,

I am working on a formula to trigger my at- risk flag based on the following information.

Raise the flag if the end date column is less that 1 day away and the status column is blank or Not Started.

Here is what I have so far.

=IF(AND(Status3 = "Not Started", [End Date]3 <= TODAY(2)), 1)

Find more posts tagged with

Formulas

functionality

Automations

Accepted answers

Paul Newcome

That is because a blank date field is considered "less than today". Try this little tweak...

=IF(AND(OR(Status@row= "", Status@row = "Not Started"), [End Date]@row <> "", [End Date]@row <= TODAY(1)), 1)

All comments

Paul Newcome

You could try something like this...

=IF(AND(OR(Status@row= "", Status@row = "Not Started"), [End Date]@row <= TODAY(1)), 1)

Krista Cornew

It worked but it marks it red even if there is no date populated yet!

Paul Newcome

That is because a blank date field is considered "less than today". Try this little tweak...

=IF(AND(OR(Status@row= "", Status@row = "Not Started"), [End Date]@row <> "", [End Date]@row <= TODAY(1)), 1)

Krista Cornew

AMAZING! That worked! thank you!

Paul Newcome

Happy to help! 👍️

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.