Formula for project health

Melissa F 13

I have the following formula and I want to add:

Turn task health Blue for START DATE column if today's date is before the start date.

Leave Task Health blank if there is not Start Date or Target End Date

=IF(OR(AND([Target End Date]@row <= TODAY(7), [% Complete]@row < 0.5), AND([Target End Date]@row < TODAY(), [% Complete]@row < 1)), "Red", IF(OR([% Complete]@row = 1, [Target End Date]@row >= TODAY(7)), "Green", IF(AND([Target End Date]@row <= TODAY(7), [% Complete]@row >= 0.5), "Yellow")))

Nick Korna

You'd just add these bits at the start to get:

=IF(OR(ISBLANK([Start Date]@row), ISBLANK([Target End Date]@row)), "", IF([Start Date]@row > TODAY(), "Blue", IF(OR(AND([Target End Date]@row <= TODAY(7), [% Complete]@row < 0.5), AND([Target End Date]@row < TODAY(), [% Complete]@row < 1)), "Red", IF(OR([% Complete]@row = 1, [Target End Date]@row >= TODAY(7)), "Green", IF(AND([Target End Date]@row <= TODAY(7), [% Complete]@row >= 0.5), "Yellow")))))

Hope this helps!

Ipshita

Hi @Melissa F 13 -

I tested out your problem and here is the solution. Use the formula below, it would turn the task health blue where date is greater then today and leave it blank if the start date is blank and you can add the logic for ned date the same way. Hope this helps!

Cheers,

Ipshita

Melissa F 13

Can you provide this added into the formula I provided above?

Nick Korna

You'd just add these bits at the start to get:

=IF(OR(ISBLANK([Start Date]@row), ISBLANK([Target End Date]@row)), "", IF([Start Date]@row > TODAY(), "Blue", IF(OR(AND([Target End Date]@row <= TODAY(7), [% Complete]@row < 0.5), AND([Target End Date]@row < TODAY(), [% Complete]@row < 1)), "Red", IF(OR([% Complete]@row = 1, [Target End Date]@row >= TODAY(7)), "Green", IF(AND([Target End Date]@row <= TODAY(7), [% Complete]@row >= 0.5), "Yellow")))))

Hope this helps!

Melissa F 13

Fantastic, thank you!