Different way to track health

Mattmohn

I have a project without a set end date and am being monitored on if I am getting it closer to completion by the % complete field, how can I make a formula that tracks health based on that forward progression so that if my % complete goes up it stays green but if it stays the same for three days it goes to yellow and if it stays the same for 7 days goes red. I do not care how much the % increases as long as it is going up every three days at minimum. I have attempted to use the date modified column to show if a row has been modified in 3 days but the date modified has been updating without me touching that row. Additionally if possible I would like a 7 day start from acceptance before the 3 days and 7 days start to account for getting the project started. Most of the tasks I have are simultaneous tasks which may eliminate being able use predecessors. Thank you!

Mattmohn

Update

The above formula I posted resolved the issue. I was using a formula to get my Date accepted but when i took the formula out and entered a date manually it worked. Thank you!

Ipshita

Hello @Mattmohn ,

I will try to answer your question here -

To track project health based on percentage completion in Smartsheet without needing an end date, you can use the % Complete column along with a formula to determine project health. You will need to define different thresholds for Complete (e.g., 50%, 75%) to assign colors (Red, Yellow, Green) to indicate project health. Here's how you can do it:

1. 1. Use a % Complete Column: Smartsheet has a built-in % Complete column or you can create your own and assign it as the Project settings. This column should show the percentage of work completed for each task. 
2. 2. Create a Health Status Column:Add a new column (e.g., Status) where you will display the health status (Red, Yellow, Green). 
3. 3. Apply a Formula:Use a formula to assess project health based on the % Complete value. For example:
   • If [Complete] is less than 50%, display Red
   • If [Complete] is between 50% and 75%, display Yellow
   • If [Complete] is greater than 75%, display Green 

The formula might look something like this (adjust the values and thresholds as needed):

    =IF([% Complete]@row < 0.5, "Red", IF([% Complete]@row < 0.75, "Yellow", "Green"))

1. Customize Your Needs: You can refine this formula to add more complex logic, like incorporating other criteria (e.g., start date, resource availability) or using different colors for different statuses. 

Example Scenario:

• If a task has a % Complete of 30%, the formula will return "Red".
• If a task has a % Complete of 65%, the formula will return "Yellow".
• If a task has a % Complete of 85%, the formula will return "Green". 

Let me know if this helps :)

Ipshita

Mattmohn

@Ipshita
Thank you this is helpful but im not sure its quite what I need.

I still would like to show that it is green even if it is below 50% as long as it is increasing every 3 days at minimum.

Ipshita

@Mattmohn in that case, you will have to incorporate that as a formula in your worksheet for that health column. Start with an IF statement and I can try to simulate that for you here but you are a Smartsheet user so I am sure you will figure it out -

=IF(VALUE(SUBSTITUTE([%Completion]@row, "%", "")) > 50, "Green", IF(AND(VALUE(SUBSTITUTE([%Completion]@row, "%", "")) < 50, VALUE(SUBSTITUTE([%Completion]@row, "%", "")) > VALUE(SUBSTITUTE([%Completion]@row - 3, "%", ""))), "Green", IF(VALUE(SUBSTITUTE([%Completion]@row, "%", "")) < 50, "Yellow", "Red")))

Let me know if this formula (or a reformed version) works for your case.

Thanks,

Ipshita

Mattmohn

I currently have this formula that is tracking if my % complete is updated within 3 days.

=IF([Date accepted]@row < TODAY() - 7, IF(AND([Date updated]@row < TODAY() - 3, [Date updated]@row > TODAY() - 7), "yellow", IF([Date updated]@row <= TODAY() - 7, "red", "green")), "green")

I want it so that if we accepted it less than 7 days ago it always shows green but if we accepted it more than 7 days ago and the date updated is between 3 and 7 days yellow and more than 7 days ago red. The part I dont have working is the date accepted part. I can mark accepted today but if the updated date is 7 days ago it still marks it red.

Mattmohn

Update

The above formula I posted resolved the issue. I was using a formula to get my Date accepted but when i took the formula out and entered a date manually it worked. Thank you!

Ipshita

@Mattmohn I am happy that the solution worked :) However, I did try to work on an alternate solution for you where if you inserted a helper column right next to your Date Accepted, you can achieve your desired responses for the Health column using the formula -

=IF([helper]@row < 3, "Green", IF(AND([helper]@row > 3, [helper]@row < 7), "Yellow", IF([helper]@row > 7, "Red")))

The formula for the helper column is a simple -

=TODAY() - [Date Accepted]@row

Hope this helps:) if it does, I would appreciate it if you marked my responses as 'awesome' to acknowledge my help so I can help more people :)

Have a great day!

Ipshita