Flag Red if under 100% or Past Due

kaela.obrien53656

Hello!

 

Here is what I am trying to accomplish!

 

In the column with the flags (highlighted yellow), I would like the flag to turn red when the Percent Complete Column (highlighted Green) is less than 100% OR if the Completion Date Column (Highlighted Blue) has a date past today's date. 

 

Please let me know if you can help with this!

 

 

Paul Newcome

Here you go...

 

=IF(OR([% Complete]@row < 1, [Completion Date]@row < TODAY()), 1, 0)

 

Smartsheet refers to percentages in formulas as actual numbers. 100% = 1, 50% = .5, 25% = .25

kaela.obrien53656

Hey Paul, 

Huge Apologies! I was posting on this forum on behalf of a manager and there was a misunderstanding when he was telling me what he intended the formula to do. 

 

So I don't know if you could help me with this, but what I really need is:

 

The flag highlights red if 1) Today's date is past the completion date (so the task is overdue) AND 2) the % Complete column is less than 100. 

 

You can use the same attached image for reference. Again, hope I didn't waste too much time on that first question! 

Paul Newcome

You would use the same formula except change the OR to AND.

 

=IF(AND([% Complete]@row < 1, [Completion Date]@row < TODAY()), 1, 0)