IF / AND formula help

Options

Hello,

I would like to add a condition to an IF formula that changes the health colour only when the Due date is not blank. Please can you assist?

At the moment the formula looks like this which works fine for tracking due dates:

=IF(Status@row = "Complete", "Green", IF([Due Date]@row = TODAY(), "Blue", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(+7), "Yellow", ""))))

However, if the due date does not have any dates in the column, the health colour turns to red. I want it to remain blank if no due date is entered but cannot think of the correct formula to achieve this.


Thanks for your help.

Tags:

Answers

  • Ipshita
    Ipshita ✭✭✭✭✭✭
    Options

    Hi @justdan2,

    Can you try either of the following formula and see if you are getting the desired results -

    1) IFERROR(value, value_if_error) - enter your formula in the value and 0 in the value_if_error

    2) ISBLANK(value) - add your entire formula in the (value)

    Hope this helps. :)

    Thanks,

    Ipshita

    Ipshita Mukherjee

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @justdan2 ,

    Is this is along the lines of what you're after:

    =IF(Status@row = "Complete", "Green", IF([Due Date]@row = TODAY(), "Blue", IF(AND(ISDATE([Due Date]@row), [Due Date]@row < TODAY()), "Red", IF(AND(ISDATE([Due Date]@row), [Due Date]@row <= TODAY(+7)), "Yellow", ""))))

    Outputs:

    Green: Status: Complete

    Blue: Today

    Yellow: Due within next 7 days

    Red: Overdue

    Clear: No due date or due date over 7 days

    If I've misunderstood some variable, let me know and the formula can be adapted to suit.

    Hope this helps; any questions etc. then just ask. 😊

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @justdan2

    As an alternative to the solution @ipshita provided when the formula throws an error, one can first check if the date column does indeed contain a date

    =IF(ISDATE([Due Date]@row), IF(Status@row = "Complete", "Green", IF([Due Date]@row = TODAY(), "Blue", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(+7), "Yellow", "")))))

    Kelly

  • justdan2
    justdan2 ✭✭✭✭
    Options

    Thanks Kelly et al, you've been very helpful. Seems like there are a number of ways to solve it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!