# 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.

Tags:

• ✭✭✭✭✭✭
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

Hope this helps. :)

Thanks,

Ipshita

Ipshita Mukherjee

• ✭✭✭✭✭✭
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. 😊

• ✭✭✭✭✭✭
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

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