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

anzar

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

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

@Paul Newcome Thank you so much. It worked!

Paul Newcome

Happy to help. 👍️

anzar

@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

@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

@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

How exactly is your Status column being populated?

anzar

It is a manual entry and looks something like this.

dhall

@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

@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

Thank you @Paul Newcome & @dhall.

It worked.

Paul Newcome

@anzar Happy to help. 👍️