formula for "IF this then this if between these 2 dates" not working

I'm trying to update and add to a formula I currently have in my sheet. This formula works for marking the health yellow if task is not started 5 days prior to or equal to due date and start date has passed.

IF(AND(Status@row = "Not started", [Due Date]@row >= TODAY(-5), [Start Date]@row < TODAY()), "Yellow"

However, the following does not work. I'm trying to mark the health as yellow if the status is not started and we are 15-19 days prior to the due date.

IF(AND(Status@row = "Not started", [Due Date]@row = TODAY(+15), [Due Date]@row < TODAY(+19)), "Yellow”

Any help is appreciated. Thanks!

Find more posts tagged with

Accepted answers

All comments

Kelly Drake

The way the formula is written you'll never end up with a date that is both the date pieces.

Try this..

=IF(AND(Status@row = "Not started", [Due Date]@row <= TODAY(+19)), "Yellow”)

This will get anything that has a due date in the next 19 days but is not started. Assuming something due in 7 days should also be flagged as "Yellow".

Theresa Bruns

Thanks Kelly. I'm trying to get to the following:

20 days prior to due date = green
15-19 days prior to due date = yellow
< 15 days prior to due date = red

With the formula you used, that will assign yellow to anything that is not started 19 days prior to due date. Do I then add a similar formula for 15 days prior?

I appreciate the 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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.