Need status icon based on work hours remaining in project

BFuller

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!

Amber Eakin

Hello @BFuller ! I don't think you'll need a Helper column. Try this formula.

=IF([Estimated PCD touch time project hours remaining]@row / [Available PCD work hours from TODAY to due date]@row >= 1.25, "Green", IF([Estimated PCD touch time project hours remaining]@row / [Available PCD work hours from TODAY to due date]@row >= 1.15, "Blue", IF([Estimated PCD touch time project hours remaining]@row / [Available PCD work hours from TODAY to due date]@row >= 1.05, "Yellow", "Red")))

Does that work for you?

BFuller

Oh my gosh! Thank you!! this worked!!! yay!!! Do you have a suggestion on how to include the Request Status being active? Should I use an AND statement?

Amber Eakin

Yes, @BFuller . What do you want the Status to be if it's not active? The formula below will work for all Active items, but it'll show a blank cell for anything not active.

=IF(AND([Estimated PCD touch time project hours remaining]@row / [Available PCD work hours from TODAY to due date]@row >= 1.25, Status@row = "Active"), "Green", IF(AND([Estimated PCD touch time project hours remaining]@row / [Available PCD work hours from TODAY to due date]@row >= 1.15, Status@row = "Active"), "Blue", IF(AND([Estimated PCD touch time project hours remaining]@row / [Available PCD work hours from TODAY to due date]@row >= 1.05, Status@row = "Active"), "Yellow", IF(AND([Estimated PCD touch time project hours remaining]@row / [Available PCD work hours from TODAY to due date]@row < 1.05, Status@row = "Active"), "Red"))))

BFuller

Thank you...I will try this. If it's not active, it's ok if it is blank.

And, I may have spoken too soon on the original formula. When I did further testing, I noticed it wasn't turning blue or yellow when the hours were within the thresholds. On these inner formulas, if it is between 115% and 124% of the Estimated PCD touch time project hours remaining, I need the status to be blue. And if it is between 105% and 114% I need it to be yellow and anything below 104% needs to be red. I tried to amend the formula, but it isn't working. Any thoughts? (And, thank you so much again!)

Amber Eakin

It's working in the little chart I made. Can you share the formula and another screenshot, please?

BFuller

Sure...Here is a screen shot. The green symbol should be red because there aren't enough hours left in the time period to finish the work according to the estimates. I will paste the screen shot of the formula below:

And here is the formula from that green line above:

Amber Eakin

Ah! I can't be trusted to do math. Oops! I had the numerator and denominator reversed. Try this!

=IF(AND([Available PCD work hours from TODAY to due date]@row / [Estimated PCD touch time project hours remaining]@row >= 1.25, Status@row = "Active"), "Green", IF(AND([Available PCD work hours from TODAY to due date]@row / [Estimated PCD touch time project hours remaining]@row >= 1.15, Status@row = "Active"), "Blue", IF(AND([Available PCD work hours from TODAY to due date]@row / [Estimated PCD touch time project hours remaining]@row >= 1.05, Status@row = "Active"), "Yellow", IF(AND([Available PCD work hours from TODAY to due date]@row / [Estimated PCD touch time project hours remaining]@row < 1.05, Status@row = "Active"), "Red"))))

BFuller

You are amazing. Thank you so much for your help. Seriously. I will give this a try!!