# IF AND with Dates

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

Tags:

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

PMP Certified

[email protected]

www.mobilproject.it

• @JmeC

The following formula will turn it blue if Status = Complete

```=IFERROR(IF(status@row = "Complete", "Blue", 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:

PMP Certified

[email protected]

www.mobilproject.it

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

PMP Certified

[email protected]

www.mobilproject.it

• OMG thank you @Bassam Khalil ! I spent HOURS trying to get it to work and you SAVED me and my sanity!! Sending you a giant virtual hug!!

• @JmeC

You are welcome and i will be happy to help you any time.

PMP Certified

[email protected]

www.mobilproject.it

• @Bassam Khalil sorry one more: where do I add the command to turn it blue if Staus = Complete, i tried at the end and it isn't working.

If the status is 'Complete' = blue

``` =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")))), "")
```

IF([status]@row = "Complete", "blue"

• I will fix it for you tomorrow because I left my office now.

PMP Certified

[email protected]

www.mobilproject.it

• @JmeC

The following formula will turn it blue if Status = Complete

```=IFERROR(IF(status@row = "Complete", "Blue", 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:

PMP Certified

[email protected]

www.mobilproject.it