Stoplight formula help

schletpe

Hi all...

I've gotten some good help on this forum before, so hopefully you all can help me out.

I'm trying to create a stoplight formula for tasks in a project plan. I would like to trigger colors of red, yellow, green based on the percentage complete of that task, and how far we are from the expected finish date of that task (column titled "Baseline Finish"). Someone here suggested I create a checkbox column that flags when today's date is within 7 days of baseline finish, which along with percentage complete would trigger the stoplight color. The formula we landed on is:

=IF(AND([7 Day Flag]@row = true, [% Complete]@row <= 0.25), "Red", IF(AND([7 Day Flag]@row = true, [% Complete]@row >= 0.75), "Green", IF(AND([7 Day Flag]@row = true, [% Complete]@row > 0.25, [% Complete]@row < 0.75), "Yellow", IF(AND[7 Day Flag]@row = false, [% Complete]@row <= 0.75), "Yellow", IF(AND([7 Day Flag]@row = false, [% Complete]@row > 0.75), "Green"))))

It was working fine until I added the "flag = false" functions, and now it is unparseable.

Does anything jump out as wrong? I'd appreciate any help. Thanks!

=Chris Palmer

Hello @schletpe

Instead of using True / False, have you tried using 1 and 0 ?

IF([This column]1=1,"true","false") will post true when the checkbox is checked.

IF([This column]1=0,"true","false") will post true when the checkbox is unchecked.

Hope this helps!

JCluff

For the formula you mentioned above, you are missing "(" in the highlighted section.

To cut out the helper column, use the below formula:

=IF(AND([Baseline Finish]@row - TODAY() <= 0, [% Complete]@row <> 1), "Red", IF(AND([Baseline Finish]@row - TODAY() <= 7, [% Complete]@row <= 0.25), "Red", IF(AND([Baseline Finish]@row - TODAY() <= 7, [% Complete]@row > 0.25, [% Complete]@row < 0.75), "Yellow", IF(AND([Baseline Finish]@row - TODAY() <= 7, [% Complete]@row >= 0.75), "Green", IF(AND([Baseline Finish]@row - TODAY() > 7, [% Complete]@row <= 0.75), "Yellow", IF(AND([Baseline Finish]@row - TODAY() > 7, [% Complete]@row > 0.75), "Green", "Green"))))))

edit - built in additional logic to show green symbol if % Complete was 100% and baseline was 0 or less.

schletpe

Thank you! Adding the missing ")" worked.