# Flag Red if under 100% or Past Due

Options
edited 12/09/19

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!

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!