# How do I have the risk flag turn red for any line with status not completed by the end date?

Options

I only have a completed date in there instead of a start and end date, is that why? I can't figure it out.

## Answers

• ✭✭✭✭✭✭
Options

Is "Completed Date" not filled out until the task is complete? If so you would need at least a target completion date column to build the formula that would trigger the flag function.

If "Completed Date" is filled out, then you can follow the instructions below, changing the syntax to fit your sheet.

Put this formula in on of the risk flag cells associated with a task. I am guessing at the names of your columns, so please adjust them accordingly.

```=IF(AND(NOT(Status@row="Complete"), TODAY()>[Completed Date]@row), 1, 0)
```

Katy Hall

Head of Product Management

ILLA Canna

LinkedIn

• ✭✭✭✭✭✭
Options

If @Katy H's formula doesn't work (I feel like it should based on my understanding of your structure), are you able to provide a screenshot for reference?

• ✭✭✭
Options

@Katy Hall - Did you ever figure this out?

• ✭✭✭✭✭✭
Options

@MelM18 What exactly are you wanting to accomplish?

• ✭✭✭
Options

@Paul Newcome Hi Paul. I wanted a simple formula that will check the At Risk flag if Health is Red. My Health column type is symbol (RYGG) and has a formula to calculate color based on start/end dates and % complete:

=IF([% Complete]@row = 1, "Green", IF([Expected % Complete]@row = 0, "Green", IF([Expected % Complete]@row < [% Complete]@row, "Green", IF([Expected % Complete]@row < [% Complete]@row, "Yellow", "Red"))))

I ended up using the same formula and changed the colors to true/false for the At Risk flag:

=IF([% Complete]@row = 1, false, IF([Expected % Complete]@row = 0, false, IF([Expected % Complete]@row < [% Complete]@row, false, IF([Expected % Complete]@row < [% Complete]@row, false, true))))

This works on child rows, but I have to remove it from parent rows because I was getting random flags.

I'm open to suggestions if there is an easier way to automate this. Thoughts?

• ✭✭✭✭✭✭
Options

If you only want it flagged when the first formula is outputting "Red", you can just do a single IF and reference the RYG column.

=IF([Symbol Column name]@row = "Red", 1)

• ✭✭✭
Options

Ugh, I was so close.. I tried

=IF([Health]@row = "Red", true, false)

Your expression worked, as always. Thank you!

• ✭✭✭✭✭✭
Options

@MelM18 Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!