Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Does anyone have the formula syntax to control the RYG and Gray symbols on a sheet
Does anyone have the formula syntax for this.... to control the RYG and Gray symbols on a sheet?
if due date < today and % complete <> 100% then red,
if due date within 5 days of today and % complete <> 100% then yellow,
if due date outside of 5 days from today % complete > 1 and < 100% then green,
if % complete is 0 and due date outside of 5 days from now then gray
Comments

I think I have formulas very close to what you are looking for. The following input columns are required (named exactly as I show):
Actual Percent Complete
Start Date
End Date
Date Completed
The following columns with the following equations are required (exactly as named here):
Days Until Start; =[Start Date]2  TODAY()
Days Until Due; =[End Date]2  TODAY()
Planned Percent Complete Per Timeline; =IF(TODAY()  [Start Date]2 < 0, 0, IF([Actual Percent Complete]2 = 1, 1, IF(((TODAY()  [Start Date]2) / ([End Date]2  [Start Date]2)) >= 1, 1, ((TODAY()  [Start Date]2) / ([End Date]2  [Start Date]2)))))
Detailed Status; =IF(AND([Actual Percent Complete]2 <> 1, [Date Completed]2 <> ""), "TASK NOT COMPLETED  DELETE EXISTING DATE", IF(AND([Actual Percent Complete]2 = 1, [Date Completed]2 = ""), "ENTER COMPLETION DATE", IF(OR(AND([End Date]2  TODAY() < 0, [Actual Percent Complete]2 <> 1), ABS([Actual Percent Complete]2  [Planned Percent Complete Per Timeline]2) >= 0.25), "In Progress  Off Track", IF([Date Completed]2  [End Date]2 > 0, "Completed  Late", IF(AND([Planned Percent Complete Per Timeline]2 = 1, [Actual Percent Complete]2 = 1), "Completed  On Time", IF(AND([Days Until Start]2 > 0, ABS([Actual Percent Complete]2  [Planned Percent Complete Per Timeline]2) <= 0.1), "Not Started", IF(AND(ABS([Actual Percent Complete]2  [Planned Percent Complete Per Timeline]2) > 0.1, ABS([Actual Percent Complete]2  [Planned Percent Complete Per Timeline]2) <= 0.25), "In Progress  At Risk", "In Progress  On Track")))))))
Status; =IF([Detailed Status]2 = "In Progress  At Risk", "Yellow", IF(OR([Detailed Status]2 = "In Progress  Off Track", [Detailed Status]2 = "TASK NOT COMPLETED  DELETE EXISTING DATE"), "Red", IF(OR([Detailed Status]2 = "Completed  On Time", [Detailed Status]2 = "Completed  Late"), "Blue", "Green")))
Note that the two "Percent Complete" columns need to be formatted as percent. The status column needs to be formatted as the symbol, specifically the red, yellow, green, and blue traffic lights. Note that these equations show red if the variance between planned and actual percent complete is more than 25%, yellow if between 10 and 25%, green if less than 10% or task hasn't been due to start yet, and blue if it is complete, either on time or late. See screenshot included below as an example.
This system has been invaluable for me and my organization in keeping on top of tasks in very large sheets as time creeps along and tasks "silently" become due. Let me know if the equations work for you.

Note that for the above equations to work, that they must be pasted into row 2. Also, be sure not to include the semicolons I included after the equationdriven column names  those could trip you up if not careful.
Also, if you want to use filters to pull for example, red and yellow rows that are tasks only (not parent rows that by themselves don't reflect real work), you will need the additional column with the additional equation, and select "0", since that identifies the row as being the "lowest level child".
Is Child?; =IF(COUNT(CHILDREN()) > 0, 1, 0)

Great stuff  thanks for this  although I do have some questions...
https://app.smartsheet.com/b/publish?EQBCT=4bfbd872ce024348bd28806d41564bd4
Row 4 has zero percent for Actual Precent Complete and this gives an #INVALID OPERATION error
The Status RYGB icons dont change dynamically
Can you summarise the AVG in the column to give an overall project Status?

First off, make sure the percent complete columns are formatted as percent  they didn't show the percent sign when I looked at your sheet. It might be possible that the equations in row 3 are seeing the Actual Percent Complete as 9000% since you have 90 in the cell, which obviously is more than a 25% variance from 0%, the current value of Planned Percent Complete, and would be why the red status symbol is showing.
Second, change the start date of your examples to be different from each other instead of all being today. This will let me know if the equations are seeing the data correctly (in the right format).
Third, it looks like you have the 1 in the Is Child? equation in the wrong spot. This makes me wonder if you didn't copy the formula to your sheet correctly  if so, this may be what happened with the other more complex equations too. Shouldn't affect the primary functionality you're asking about, but thought I'd point it out nonetheless.

Hi
I have updated as per your comments...
1. changed values to % and this corrted some of them
2. changed the start dates to varied dates
3. changed the end dates to varied dates
4. the IS CHILD value was simply me reversing the logic to show the sub taska as 1 and the parent as 0. I have reverted this logic now
Is appears that if the start date is in teh future then you get the #INVALID OPERATION error... any thoughts?

It looks like planned percent complete is still not in % format

So I am trying to understand some of the logic you are using.
1. Planned Percent complete per timeline. So I don't quite understand this calculation. Looking at my picture below I have my actual percentage complete as 95%. How are you getting the number 46%? I am still within the start and end dates.
2. And why is the detailed status In ProgressOff Track? Again I am 95% done and within my end date window. Why is it considered 'offtrack'?
I really like this but just trying to understand your logic and calculations.


So playing around with this it seems my sums are off and backward. when the Planned Percent Complete says 9% and my actual Percent Complete says 60% the Detailed status = In ProgressOff Track.
But when I change Actual Percent Complete to 8% (keeping Planned at 9%), detailed status = In ProgressOn Track.
I also want when the Acutal Percentage Complete is= 0% and the start date has passed, that Detailed Status says Not StartedAt Risk and turns Status to Red.
Here are my calculations.
Planned Percent Complete Per Timeline
=IF(TODAY()  [Start Date]2 < 0, 0, IF([Actual Percent Complete]2 = 1, 1, IF(((TODAY()  [Start Date]2) / ([End Date]2  [Start Date]2)) >= 1, 1, ((TODAY()  [Start Date]2) / ([End Date]2  [Start Date]2)))))
Detailed Status
=IF(AND([Actual Percent Complete]2 <> 1, [Date Completed]2 <> ""), "TASK NOT COMPLETED  DELETE EXISTING DATE", IF(AND([Actual Percent Complete]2 = 1, [Date Completed]2 = ""), "ENTER COMPLETION DATE", IF(OR(AND([End Date]2  TODAY() < 0, [Actual Percent Complete]2 <> 1), ABS([Actual Percent Complete]2  [Planned Percent Complete Per Timeline]2) >= 0.25), "In Progress  Off Track", IF([Date Completed]2  [End Date]2 > 0, "Completed  Late", IF(AND([Planned Percent Complete Per Timeline]2 = 1, [Actual Percent Complete]2 = 1), "Completed  On Time", IF(AND([Days Until Start]2 > 0, ABS([Actual Percent Complete]2  [Planned Percent Complete Per Timeline]2) <= 0.1), "Not Started", IF(AND(ABS([Actual Percent Complete]2  [Planned Percent Complete Per Timeline]2) > 0.1, ABS([Actual Percent Complete]2  [Planned Percent Complete Per Timeline]2) <= 0.25), "In Progress  At Risk", "In Progress  On Track")))))))
Any help is appreciated.

I didn't look through the equations in detail  I assume they are not modified to what I shared initially. However, it sounds as though the equations are working as expected congrats! Technically if the Actual is greater than the Planned by 25%, the task is off track (in the strictest project management sense  work is being done more quickly than planned). In the rare event work on my projects is in this situation, I reduce the duration, or if the end date should remain, simply live with the red until the Planned "catches up". One could very well change the equation to only show red if the Planned is 25% greater than Actual  I just don't have this need. The equation is already long enough, I'm trying to keep it "simple".
As for your last additional requirement, I politely "challenge" you to see if you can add it yourself  you seem pretty astute at the equations. I feel that if the start date passes and Actual stays 0, status will turn yellow and eventually red soon enough on its own to warrant avoiding the additional code. It may be that instead of the variance waiting to get to 10% before yellow appears, that a lower percentage would better suit your needs.
Glad it is working for you!

Hugely appreciated  thanks

Mike thanks. I am getting an error #INvalid Operation. Do you know why? I used your formulas and set up....

I would suggest that you need to have a value other than zero in the Planned Percent Complete Per Timeline.
If you compare the row that has 6% in it this is not erroring.

If you wouldn't mind sharing your sheet with me (mike.andreas@tarkett.com), I will take a look at it. Make sure that you are dragging the equations down using the lower right corner of the cell as opposed to copying/pasting in each cell, which would result in those equations referring back to the values in row 2. This may explain why your first row is working and the rest of them aren't.

I got it working  you didn't have "Date Completed" column formatted as a date  it was Text. Voila!