Hello,
I have product content developers (PCDs) who are assigned and manage projects in Smartsheet. We have estimated PCD touch time hours for each project. The PCDs log in their hours when they work on a project and that time gets deducted from the Estimated PCD touch time hours for project column.
Example: Estimated PCD touch time hours for project minus Actual PCD touch time hours equals Estimated PCD touch time project hours remaining
I have calculated the number of work hours available from Today through the due date of the assignment. I would like to add a Touch time status symbol that would automatically change colors based on the following criteria for all "active" requests (I have a column for Request status and it can be either Active, On Hold, Cancelled, Completed):
If request status is "Active" and the work hours available is 125% or more of the Estimated PCD touch time project hours remaining, status is green.
If request status is "Active" and the work hours available is between 115% and 124% of the Estimated PCD touch time project hours remaining, status is blue
If request status is "Active" and the work hours available is between 105% and 114% of the Estimated PCD touch time project hours remaining, status is yellow
If request status is "Active" and the work hours available is below 105% of the Estimated PCD touch time project hours remaining, the status is red.
I'm trying to do this in as few columns as possible, but I'm thinking I am going to have to create helper columns with the percentages calculated. Below is a screen shot of what I currently have with test data. I could use some help developing the formula to accomplish this.
Many thanks in advance!
