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

Options
✭✭✭✭

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

1. Don't highlight the red flag if the status is N/A or Completed
2. 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)))

Tags:

• ✭✭✭✭✭
Options

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

1. The first case checks for "N/A" or "Completed" and sets them to 0 (False) ALWAYS
2. 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
3. If it is less than or equal to 3 days in the future then Flag IT TRUE. This also gets anything in the Past
4. Everything Else gets a 0

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

1. The first case checks for "N/A" or "Completed" and sets them to 0 (False) ALWAYS
2. 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
3. If it is less than or equal to 3 days in the future then Flag IT TRUE. This also gets anything in the Past
4. Everything Else gets a 0

Brent C. Wilson, P.Eng, PMP, Prince2

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭