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

anzar · 08/05/22

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.

Paul Newcome · 08/05/22

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

anzar · 08/19/22

@Paul Newcome Thank you so much. It worked!

Paul Newcome · 08/19/22

Happy to help. 👍️

anzar · 08/29/22

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

Paul Newcome · 08/29/22

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

anzar · 08/30/22

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

Paul Newcome · 08/30/22

How exactly is your Status column being populated?

anzar · 08/30/22

It is a manual entry and looks something like this.

dhall · 08/30/22

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

Paul Newcome · 08/30/22

@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%.

anzar · 09/01/22

Thank you @Paul Newcome & @dhall.

It worked.

Paul Newcome · 09/01/22

@anzar Happy to help. 👍️

Formulas and Functions

Formulas and Functions

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

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed

Formula for averaging days