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

Jharris11

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

Katy H

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)

Paul Newcome

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?

MelM18

@Katy Hall - Did you ever figure this out?

Paul Newcome

@MelM18 What exactly are you wanting to accomplish?

MelM18

@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?

Paul Newcome

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)

MelM18

Ugh, I was so close.. I tried

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

Your expression worked, as always. Thank you!

Paul Newcome

@MelM18 Happy to help. 👍️