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.
The Smartsheet Solution
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.
-
- Calculate “Project Finish Duration”, or “Planned Finish” – “Planned Start”
- Smartsheet formula
=[Planned Finish]@row – [Planned Start]@row - Example
Planned Start is 1/1/2021 and Planned Finish is 6/30/2021, so the Project Finish Duration is 180 days.
- Smartsheet formula
- Calculate the “Planned Days into Project”, or how many days into the project based on today’s date and “Planned Start”.
- Smartsheet formula
=TODAY() – [Planned Start]@row - 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.
- Smartsheet formula
- Calculate the “Actual Duration”, or how may actual days into the project based on the percent complete of the project.
- Smartsheet formula
=ROUNDUP([Project Finish Duration]@row * [% Complete]@row) - 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.
- Smartsheet formula
- Calculate the “Project Duration Variance”, which is the difference between the planned duration and the actual duration.
- Smartsheet formula
=[Planned Days into Project]@row – [Actual Duration]@row - Example
Planned Days into the project is 83 and the actual duration is 81 days, so the project is two (2) days late.
- Smartsheet formula
- Calculate the “Project Health” using the Problem Statement.
- 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”))). - Example
Since the project is two (2) days late, the project health is Yellow.
- Smartsheet formula
- Calculate “Project Finish Duration”, or “Planned Finish” – “Planned Start”
Let Rego Be Your Smartsheet Guide
For more smart solutions for Smartsheet, connect with one Rego’s expert Smartsheet practitioners today by contacting us today.
Rego also offers free digital ecosystem assessments, webinars, and half-day training classes. For a full list of Rego services, please visit regoconsulting.com.