IF(AND not working as expected

AJCruz

Greetings,

I have a project with Start/End date (Type: Date/Time) column, Status column (Type: Dropdown), along with a project health column (Type: Symbol). I am looking to control the color of the harvey balls from Gray, Red, Yellow, and Green depending on Date AND status such as

If the status is complete, then gray

If the start date is within 7 days AND the status is Not Started, then Yellow

If the End Date is in the past AND status is not yet Complete, then Red

This works fine and what's in there now:

=IF(Status@row = "Complete", "Gray", IF([Start Date]@row >= TODAY(+14), "Green", IF([Start Date]@row >= TODAY(+7), "Yellow", "Red")))

The following gives me a "Invalid Data Type" error:

=IF([Status]@row = "Complete", "Gray", IF(and([Start Date]@row >= TODAY(+7), Status@row = "Not Started", "Yellow"), If(and([End Date]@row >= Today(), status@row <> "Complete", "Red"), "Green")))

Any help is appreciated

Melissa Boehl

@AJCruz I actually checked into this more and I recommend using this formula to get the results you are looking for. =IF(Status@row = "Complete", "Gray", IF(AND([End Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Start Date]@row < TODAY(), Status@row = "Not Started"), "Red", IF(AND([Start Date]@row <= TODAY(7), Status@row = "Not Started"), "Yellow", "Green"))))

Melissa Boehl

Remove the + from the TODAY(+7). it automatically calculates 7 days into the future when entering a positive number.

AJCruz

Thank you Melissa,

Still getting "invalid data type" error after change which is odd since there is a current nested IF statement already there and just adding an additional condition.

Formula as entered:

=IF([Status]@row = "Complete", "Gray", IF(and([Start Date]@row >= TODAY(7), Status@row = "Not Started", "Yellow"), If(and([End Date]@row >= Today(), status@row <> "Complete", "Red"), "Green")))

Melissa Boehl

@AJCruz Your parenthesis are incorrect. Your last "and" statement should have a closing parenthesis after "Complete". Try that then let me know if you still have trouble

Melissa Boehl

@AJCruz I actually checked into this more and I recommend using this formula to get the results you are looking for. =IF(Status@row = "Complete", "Gray", IF(AND([End Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Start Date]@row < TODAY(), Status@row = "Not Started"), "Red", IF(AND([Start Date]@row <= TODAY(7), Status@row = "Not Started"), "Yellow", "Green"))))

AJCruz

@Melissa Boehl

Hello Melissa

UNPARSEABLE error after making change.

Regards,

AJCruz

Woops, looks like our updates crossed

I'll try that

Melissa Boehl

@AJCruz Did you see the formula I just added? Try using that. You have many parenthesis issues in there. Here it is again.. =IF(Status@row = "Complete", "Gray", IF(AND([End Date]@row <= TODAY(), Status@row <> "Complete"), "Red", IF(AND([Start Date]@row < TODAY(), Status@row = "Not Started"), "Red", IF(AND([Start Date]@row <= TODAY(7), Status@row = "Not Started"), "Yellow", "Green"))))

AJCruz

Thank you Melissa!

Like a charm. Much appreciated

Melissa Boehl

@AJCruz Your very welcome! Please be sure to mark my response as the answer to your question and have a wonderful rest of your day!