How to I change the Schedule Heath formula on the Project Plan from the PMO Template Set?

WDE

Hello, I am using the PMO Template Set from Smartsheet and am having trouble figuring out how to write a formula to change the Schedule Heath column to capture the following:

If the Status is flipped to "Complete" =Green.

If today is 5 days or fewer from the start date and until the end date OR the Status is flipped to "Complete" = Yellow.

If today is the day after the end and until status is flipped to "Complete" = Red.

If tried to use a few of the other functions that appear to be similar to my situation on this forum but....still can't get it to work.

Help!

Paul Newcome

Try this:

=IF(OR([Status]@row = "Complete", [Start Date]@row> TODAY(5)), "Green", IF([End Date]@row< TODAY(), "Red", "Yellow"))

Paul Newcome

What if the status is not complete but the end date is more than 5 days in the future?

WDE

@Paul Newcome great question. I hadn't thought about that but probably green.

Paul Newcome

Try this:

=IF(OR([Status]@row = "Complete", [Start Date]@row> TODAY(5)), "Green", IF([End Date]@row< TODAY(), "Red", "Yellow"))

WDE

THANK YOU! That worked.

Paul Newcome

Happy to help. 👍️