# Schedule Health based on End Date, Target End date and Todays date.

Options
✭✭

Need a formula for schedule health based on these conditions

Schedule Health- Red: When end date is greater than target end date and in case end date is blank, it should turn red if todays date is greater than target end date

Schedule Health - Yellow : In case end date is left blank and target end date is in next 4 days

Schedule Health - Green : When End date is less than target end date.

Thanks!

Feel free to share a different logic for the same problem statement.

Tags:

• ✭✭✭✭✭✭
Options

Try this:

=IF([End Date]@row = "", IF(TODAY() > [Target End Date]@row, "Red", IF(TODAY(4) > [Target End Date]@row, "Yellow")), IF([End Date]@row > [Target End Date]@row, "Red", "Green"))

• ✭✭
Options

@Paul Newcome Thank you so much. It worked!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭
Options

@Paul Newcome I want to add status column to this formula too. If the Status is 100%, Schedule health should be green even if the end date exceeds the target end date.

• ✭✭✭✭✭✭
Options

@anzar It would look more like this the:

=IF(Status@row = 1, "Green", IF([End Date]@row = "", IF(TODAY() > [Target End Date]@row, "Red", IF(TODAY(4) > [Target End Date]@row, "Yellow")), IF([End Date]@row > [Target End Date]@row, "Red", "Green")))

• ✭✭
Options

@Paul Newcome Thank you but Schedule health is still showing me red if the status is 100% and End date exceeds target end date.

My requirement is that the Schedule Health should be green, when Status is 100% even if my end date exceeds target end date.

• ✭✭✭✭✭✭
Options

How exactly is your Status column being populated?

• ✭✭
Options

It is a manual entry and looks something like this.

• ✭✭✭✭
Options

@anzar , using @Paul Newcome's formula, you need to change the 1 to "Complete"

=IF(Status@row = "Complete", "Green", IF([End Date]@row = "", IF(TODAY() > [Target End Date]@row, "Red", IF(TODAY(4) > [Target End Date]@row, "Yellow")), IF([End Date]@row > [Target End Date]@row, "Red", "Green")))

• ✭✭✭✭✭✭
Options

@anzar Yes. You need to evaluate for what is actually in the Status column. SO if the Status column holds "Complete" then you would need to evaluate for "Complete" as opposed to 100%.

• ✭✭
Options

Thank you @Paul Newcome & @dhall.

It worked.

• ✭✭✭✭✭✭
Options

@anzar Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!