IF AND with Dates

JmeC

Help! I've searched and I am stuck! I'm new to SS and I cannot get any formula to work. Here's what I'm trying to do;

If the status is "In Process" and today's date is greater than 7 days from the due date = Green

If the status is "In Process" and today's date is within 7 days of the due date = yellow

If the status is "In Process" and today's date is equal to or after the due date = red

If the status is 'Complete' = blue

Bassam Khalil

Hi @JmeC 

Hope you are fine, please try the following formula and convert it to column format formula:

=IFERROR(IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress",
[due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress",
[due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow",
IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red")))), "")

the following screenshot shows the result: