Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
My first automatic RYG/RAG schedule status formulas
This is my first new discussion. I wanted to share some automation I put into my schedule that made it much more friendly.
First, I have a few key columns:
- Status (Drop-down, manual entry, "Not Started" or "In Progress" or "Completed")
- % (Manual, progress on completing a task)
- RAG (Automatic, shows Red, Amber or Green based on a formula)
- StatusCalc (Text field, Automatic, Key formula to determine whether a task is Warning or Critical)
- Start/End Dates (Standard fields, used in calculations)
=IF(OR(Status1 = "Complete", [End Date]1 = [Start Date]1), "", IF(AND([End Date]1 < (TODAY(0) + 1), %1 < 1), "Critical", IF(OR(AND(OR(Status1 = "", Status1 = "Not Started"), [Start Date]1 < TODAY(0)), (TODAY(0) - [Start Date]1) / ([End Date]1 - [Start Date]1) > %1), "Warning", "")))
To break it down, it goes like this:
- If the status has manually been set to "Complete" or it's a milestone (0 day activity), I assume it is not in Warning or in Critical. For some, milestones may still be warning or critical, but without baseline versus actual calculations I'd rather focus on the tasks.
- If the end date is tomorrow or sooner, and % is less than 100%, I flag the task Critical.
- The next one is the trickiest. It has 2 conditions for Warning:
- Status is blank or "Not Started", and start date was earlier than today, it's Warning.
- The next formula calculates the percentage of time elapsed versus the percentage complete. If percent elapsed is more than percent complete (I.e. more time has run out than work completed), it goes into Warning. This is helpful for focusing on the challenged tasks.
Next, I have a simple formula in the RAG/RYG column:
=IF(StatusCalc1 = "Warning", "Yellow", IF(StatusCalc1 = "Critical", "Red", "Green"))
It's pretty basic, and sets the colour of the flag to Red, Yellow/Amber or Green based on the previous status calculation.
Finally, I set a column and gantt conditional formatting based on the RAG to highlight warning tasks a lighter yellow and critical tasks a lighter red. This makes it very easy to quickly review the schedule and identify problem tasks to focus on.
I hope this helps some of you get some additional functionality out of this tool.