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! 


  • Kelly Drake
    Kelly Drake Overachievers Alumni

    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".

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!