If Date is between Today and 4 Days from Today = YELLOW

Reggie Andaya

I've been looking at the different threads for an example that I can use for this formula but could not find anything that helps.

What I'm looking for is if the END DATE is less than today, RED. If its greater than today and less than 4 days in the future, YELLOW, otherwise GREEN. I'm getting YELLOW for any date <= TODAY. END DATES for 10/24/22 should be GREEN.

=IF([End Date]4 < TODAY(), "Red", IF([End Date]4 >= TODAY(-4)), "Yellow", "Green")

Sameer K

This may help,

=IF([End Date]@row < TODAY(), "Red", 
    IF([End Date]@row - TODAY() < 4, "Yellow", "Green")
)

Reggie Andaya

@Sameer K Thank you so much, That worked.

edit:

If I wanted to add an extra step, to say add the color code only if Status3 is not COMPLETE. If STATUS is COMPLETE, then no color code. This didn't work for me.

IF([End Date]3 < TODAY(), "Red", NOT(Status@row = "Complete"), IF([End Date]3 - TODAY() < 4, NOT(Status@row = "Complete")"Yellow", IF([End Date]3 - TODAY() > 4, NOT(Status@row = "Complete")"Green"), IF(Status@row = "Complete")""))

Sameer K

In that case it will be,

=IF(Status@row <> "Complete",
    IF([End Date]@row < TODAY(), "Red", 
        IF([End Date]@row - TODAY() <4, "Yellow", "Green")
    )
)