Actual vs target Date


Need a help

if my Start Date is 05/24/2021 and end date is 07/01/2021

and in my project plan sheet say i have a task that start on 06/24/2021 and end on 06/28/2021 .However, that task is already been completed and the actual % Complete has been updated to 60% for that task i want the schedule Health should be turn to Green for that task.

can someone help me with a formula .

As now its changing to red.

=IF(Status@row = "In Progress", "Red", IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", "Gray", IF(Status@row = "On Hold", "Yellow", ""))))


  • Jen Lange
    Jen Lange ✭✭✭✭✭

    Hi @Finops C'ship. I'm confused by the scenario. Can you share a screenshot of the row in question.

    My confusion is in the value of 60%. If the status of the task is "Complete", shouldn't the % Complete be 100%? Also, based on the formula you referenced, the % complete has no impact.

    So, my assumption based on what you've shared is that for some reason this completed task is still showing a task status of "In Progress".

    For this reason, I think a screen capture will provide clarity and guide a resolution to your inquiry.



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Finops C'ship

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!