Formula for at-risk flag, due within 3 days

Hi!

I would like to have my at-risk flag turn red when 1) a due date is within 3 days or past the due date and 2) its not completed. I am using the below formula but it doesn't work:

=IF(AND([End Date]3 < TODAY(-3), NOT(Status3 = "Complete")), 1, 0)

I tried it with making it TODAY() and it works for overdue items, but I'd like a little more "heads up".

Thanks for any suggestion!

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Christina09

Hi @learnSmart

Your formula looks like it will work, not sure why it didn't. Anyways, try this:

=IF(AND([End Date]@row < TODAY(-3), Status@row <> "Complete"), 1, 0)

Hope this works for you.

learnSmart

Unfortunately that didn't work but I think it helped me get to more understanding so I think I figured it out. I changed the (-3) to (3), so I believe I was thinking about it incorrectly. I wanted to be notified of a date three days *in the future* which is positive as opposed to three days past which would be a negative approach. It's still acting a little funny with dates around the weekend but I think changing from negative to positive works. Thank you!

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.