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:

Bassam Khalil

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

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:

JmeC

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!!

Bassam Khalil

@JmeC

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

JmeC

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

Bassam Khalil

@JmeC

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

Bassam Khalil

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