IF(AND not working as expected

Options
AJCruz
AJCruz ✭✭
edited 07/20/22 in Formulas and Functions

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

Best Answer

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    Smartsheet Architect | TurningPoint Energy

Answers

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    Options

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

    Melissa Boehl

    Smartsheet Architect | TurningPoint Energy

  • AJCruz
    AJCruz ✭✭
    Options

    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
    Melissa Boehl ✭✭✭✭✭✭
    edited 07/21/22
    Options

    @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

    Smartsheet Architect | TurningPoint Energy

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    Smartsheet Architect | TurningPoint Energy

  • AJCruz
    AJCruz ✭✭
    Options

    @Melissa Boehl

    Hello Melissa

    UNPARSEABLE error after making change.

    Regards,

  • AJCruz
    AJCruz ✭✭
    Options

    Woops, looks like our updates crossed

    I'll try that

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    edited 07/21/22
    Options

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

    Melissa Boehl

    Smartsheet Architect | TurningPoint Energy

  • AJCruz
    AJCruz ✭✭
    Options

    Thank you Melissa!

    Like a charm. Much appreciated

  • Melissa Boehl
    Melissa Boehl ✭✭✭✭✭✭
    edited 07/21/22
    Options

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

    Melissa Boehl

    Smartsheet Architect | TurningPoint Energy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!