# Formula for project health

edited 02/23/23

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")))

• ✭✭✭✭✭✭

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!

• ✭✭✭✭✭✭

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

Ipshita Mukherjee

• edited 02/23/23

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

• ✭✭✭✭✭✭

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!

• Fantastic, thank you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!