IF AND date issue

AndrewC

Hello,

I hope you can help please.

We use Smartsheet to prioritise our workload. What we want to do is to highlight if a task is due this week but we have not manually indicated it as a priority for this week.

I have written an IF AND to use a red indicator if the conditions above exist and green if not. It seems to work until there is no date specified. 

The formula I have used is: =IF(AND(Start9 - 5 < TODAY(), Priority9 > 1), "Red", "Green")

Can anyone help with the formula/expression I should use please?

Thanks

Paul Newcome

Blank date cells are considered to be less than TODAY(), so we just need to specify to the formula that it only runs when the date cell contains a date.

 

=IF(AND(ISDATE(Start9), Start9 - 5 < TODAY(), Priority9 > 1), "Red", "Green")