IF / AND formula help

justdan2

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.

Ipshita

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

Nick Korna

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

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

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