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

Tony Fronza

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

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

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

Tony Fronza

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

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

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

Tony Fronza

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