How do you set a flag based on status and days within a due date?

I'm trying to create a formula with the following logic:

Don't highlight the red flag if the status is N/A or Completed
Highlight the red flag If the status is In Progress or Not Started and the due date is <= Today (-3 days)

Below is what I'm using, but now none of the flags are turning red (and no error message appears):

=IF(OR(Status@row = "N/A", Status@row = "Completed"), 0, IF(AND(Status@row = "In Progress", [Due Date]@row <= TODAY(-3)), 1, IF(AND(Status@row = "In Progress", [Due Date]@row <= TODAY(-3)), 1)))

Find more posts tagged with

Formulas

Accepted answers

Brent Wilson

@Tony Fronza

Today(-3) is 3 days in the past

I am guessing by your last comment that you want Today(3) which is 3 days in the future

Try

=IF( OR(Status@row = "N/A", Status@row = "Completed"), 0, IF( [Due Date]@row <= TODAY(3), 1, 0))

The first case checks for "N/A" or "Completed" and sets them to 0 (False) ALWAYS
Then the second just checks for the Date as everything else would be Not Started or In Progress as we have ruled out NA and Complete in (1) no need to check again. If you have other cases then I oversimplified
If it is less than or equal to 3 days in the future then Flag IT TRUE. This also gets anything in the Past
Everything Else gets a 0

Tony Fronza

You da man! Thanks, @Brent Wilson! As usual, I was making things more complicated (but learning).

All comments

Tony Fronza

To clarify #2 above, I'm looking to flag a task if the due date is within three days and the status is either In Progress or Not Started. And, I guess I should also add the logic to flag a task if the due date is in the past.

Brent Wilson

@Tony Fronza

Today(-3) is 3 days in the past

I am guessing by your last comment that you want Today(3) which is 3 days in the future

Try

=IF( OR(Status@row = "N/A", Status@row = "Completed"), 0, IF( [Due Date]@row <= TODAY(3), 1, 0))

The first case checks for "N/A" or "Completed" and sets them to 0 (False) ALWAYS
Then the second just checks for the Date as everything else would be Not Started or In Progress as we have ruled out NA and Complete in (1) no need to check again. If you have other cases then I oversimplified
If it is less than or equal to 3 days in the future then Flag IT TRUE. This also gets anything in the Past
Everything Else gets a 0

Tony Fronza

You da man! Thanks, @Brent Wilson! As usual, I was making things more complicated (but learning).

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.