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

Theresa Bruns

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! 

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.