Smart Solutions for Smartsheet: Going Beyond Red, Amber, and Green

Whatever your industry or maturity level, Smartsheet helps you drive enterprise efficiency and agility. It’s a powerful solution built to grow in response and adapt to your needs by providing the key ingredients to success, including one that’s sometimes overlooked–flexibility.

Because Rego expert guides have boots-on-the-ground experience as Smartsheet implementers and administrators for every kind of company, we have first-hand knowledge about that flexibility. So, when a customer reaches out for a Smartsheet solution to the following scenario, it’s familiar and easily remedied:

Let’s say you’re a Program Manager who needs to demonstrate Project Health in an objective format instead of relying on subjective and non-standardized Green, Amber, and Red criteria from the project management team.

After meeting with Project Managers, you reach an organization-wide consensus on how to label projects:

• Green label: Projects that are projected to finish early or on time
• Amber/Yellow label: Projects that are expected to finish one to four days late
• Red label: Projects that will finish five or more days late

One of Smartsheet’s many fantastic features is that there are many ways to solve a problem. We built the following Smartsheet solution to give users multiple columns that could be used for other purposes, such as reports or dashboards. Our solution also eliminates massive formulas that can be difficult to debug, especially when items change, such as range or criterion.

Now, let’s outline the solution with five simple steps you’ll need to follow in Smartsheet.

Note: Column Headers are in quotes (“ ”) and Smartsheet formulas are indicated in orange.  A specific example follows each step.

1. 1. Calculate “Project Finish Duration”, or “Planned Finish” – “Planned Start”
      1. Smartsheet formula
         =[Planned Finish]@row – [Planned Start]@row
      2. Example
         Planned Start is 1/1/2021 and Planned Finish is 6/30/2021, so the Project Finish Duration is 180 days.
   2. Calculate the “Planned Days into Project”, or how many days into the project based on today’s date and “Planned Start”.
      1. Smartsheet formula
         =TODAY() – [Planned Start]@row
      2. Example
         Today is 3/25/2021 and the Planned Start is 1/1/2021, so “Planned Days into Project” is 83 days. By using the TODAY function, this solution is current from day to day.
   3. Calculate the “Actual Duration”, or how may actual days into the project based on the percent complete of the project.
      1. Smartsheet formula
         =ROUNDUP([Project Finish Duration]@row * [% Complete]@row)
      2. Example
         Project Finish Duration is 180 days. The percent complete of each task in the project rolls up to 45% complete for the project. So, the actual days is 180 days times 31%, and the actual duration is 81 days.
   4. Calculate the “Project Duration Variance”, which is the difference between the planned duration and the actual duration.
      1. Smartsheet formula
         =[Planned Days into Project]@row – [Actual Duration]@row
      2. Example
         Planned Days into the project is 83 and the actual duration is 81 days, so the project is two (2) days late.
   5. Calculate the “Project Health” using the Problem Statement.
      1. Smartsheet formula
         =IF([Project Duration Variance]@row <= 0, “Green”, IF(AND([Project Duration Variance]@row > 0, [Project Duration Variance]@row <= 4), “Yellow”, IF([Project Duration Variance]@row > 4, “Red”))).
      2. Example
         Since the project is two (2) days late, the project health is Yellow.

Rego also offers free digital ecosystem assessments, webinars, and half-day training classes. For a full list of Rego services, please visit regoconsulting.com.