Automating RYG balls

Travis

Hi Tariq, 

 

This is how you would use the AND function:

 

Syntax: AND(boolean_expression1, boolean_expression2, boolean_expression3, ...)

 

Actual:

 

=IF(AND(Complete1 = 1, [Due Date]1 > TODAY()), "Green")

 

This can be added to your larger formula the same way other functions are. 

Jes

I, too, am having Automation issues with the RYG. I am trying to use the templates, videos and examples provided, but they seem to be going over my head, or I am just flat out missing something. I want my RYG to Auto change based on % complete. Basically, I want "<50%" to be Red, Between "51% - 75%"  to be Yellow and "> 90%" to be Green.

 

I tested this with the Parent row (Which Auto generates the total % Compete based off the subtasks.) by using a simple one part formula of =IF([% Complete]5 < 50 (), "Red")  - This formula gets me "Operator Expected".

 

I have tried spacing issues  (=IF([% Complete]5 < 50(), "Red") .

 

I have tried adding the % Sign (=IF([% Complete]5 < 50(), "Red")  - Gets me message "Unparseable".

 

I have tried countless iterations of this formula and just keep getting error codes... Please Advise. I know I have to be oh so close and it is definitley an ID-10-T error, but I just can't nail it down.

Kennedy Stomps

Hi Jes-- Try this:

 

=IF([% Complete]5 < .5, "Red")

 

You'll want to use the decimal values for your percentage complete comparisons, and remove those extraneous parentheses from your original formula. I think this should resolve the error!

djm714

I am having an issue when trying to have the RYG work off of end dates and & complete. I want to have the project green if the date is out say 5 days and the percent complete is above 75% for example.  If the project is 2 days out and below 75% then it should be yellow. If the project is on the due date and not complete it should be yellow until the date passes and then becomes red. I have been playing with formulas for a while now and cant get it to work. 

Jennifer Greenough

Travis - is there any way to have 5 colors of Harvey balls?  In our Sharepoint Lists we use Grey for complete and black for "on hold" or "No Longer Applicable", so we are used to having 5 colors.  I saw grey is a 4th color for one list and grey as the 4th for another.  Any way to have R,Y,G,B,Grey to get 5?

Thanks!

Sarah Geary

I'm using this as a base, but I would like to add on that if the Status column is "Complete", then the ball is Gray.  I'm unsuccessful so far - any idea of how to fix it?

 

=IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green")))

 

I tried:

=IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Green", IF([Status] = Complete(), "Gray")))

 

Mike Andreas

I posted this answer on another thread, but I think it may help to repost it here.

 

For my system to work, the following input columns are required (named exactly as I show).  Note that I use the RYG and Blue (for completed tasks, as opposed to gray) system.

 

Proj Mgr Curr Conf Level of On Time Completion (Drop down - see details below)

Actual Percent Complete (formatted as percent)

Start Date (formatted as date)

End Date (formatted as date)

Date Completed (formatted as date)

 

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([Planned Percent Complete Per Timeline]2 - [Actual Percent Complete]2 > 0.1, [Planned Percent Complete Per Timeline]2 <> 1, [Proj Mgr Curr Conf Level of On Time Completion]2 = "Medium"), "In Progress - At Risk (Medium Confidence)", IF(AND([Planned Percent Complete Per Timeline]2 - [Actual Percent Complete]2 > 0.1, [Planned Percent Complete Per Timeline]2 <> 1, [Proj Mgr Curr Conf Level of On Time Completion]2 = "High"), "In Progress - On Track (High Confidence)", 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(OR([Detailed Status]2 = "In Progress - At Risk", [Detailed Status]2 = "In Progress - At Risk (Medium Confidence)"), "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")))

 

Again, 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.

 

Take care to keep an eye on the rows with Detailed Status of "...High/Medium Confidence)", as Planned Percent Complete can creep to 100%, but the Status ball will show Yellow or Green up to PPC = 99%.  To be clear, if PPC = 100% but the actual is anything less, the Red status ball DOES appear.

 

 

Note that for the above equations to work, that they must be pasted into row 2.  Also, be sure not to include the semi-colons I included after the equation-driven 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)

 

Note about "Confidence..." column:

A situation may exist where a task should be 90% complete according to the timing defined (ex: given duration is 10 days, and 9 days have passed), has an effort of 2 hours, but is currently 0% complete.  In this scenario, my above equations would return the red status ball, but it may be very possible that the work was always planned to complete on the last day of the 10 days.

 

This is a drop down field, with the values of N/A, Low, Medium, and High.  The way I am using this is to assign N/A to parent rows and also tasks with Detailed Status of "Not Started".  For tasks that have a Planned vs Actual % Complete variance of greater than 10%, PPC not equal to 100%, and an on time confidence level of Medium, Detailed Status results in "...(Medium Confidence)" per below.

 

 

Similar parameters, but confidence is "High", results in the following:

 

Note the conditional formatting I applied to the confidence level column, with the rules as shown:

 

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.

AC1

Hi,

I have used the formula: =IF(Finish78 < TODAY(), "No") to populate an 'overdue alert' column with a red cross icon automatically when the finish date passes. However, some items in my sheet do not contain start/finish dates as they are not yet scheduled. These rows are showing as "no" in the 'overdue alert' column.

How do I stop this?

 

Thanks,

Tori

Sigi

Hi,

I would like to have "Green" shown if the "start date" is in the future. If the "start date" is either today or in the past and the "end date" is either today or in the future, I would like to have "Yellow" shown. If the "end date" is passed I would like to have "Red" shown. If "completed" is ticked it should turn "Blue" no matter the date.

How can I achieve this?

Best regards,

Sigi

Patrick Galvin

Hi 

I used this formula and it works a treat: 

=IF(AND([End Date]9 < TODAY(), [% Complete]9 < 1), "Missed or Not Started", IF([% Complete]9 = 0, "Not started", IF([% Complete]9 < 1, "In Progress", IF([% Complete]9 = 1, "Completed", "error"))))

to extend this, I wanted to flag up tasks that were "missed" so past their End Date and tasks where the End Date is still in the future, but % Complete was still zero

=IF(AND([End Date]10 < TODAY(), [% Complete]10 < 1), "Missed", IF([End Date]10 > TODAY(), [% Complete]10 < 1), "In Progress", (IF([% Complete]10 = 0, "Not started", IF([% Complete]10 < 1, "In Progress", IF([% Complete]10 = 1, "Completed", "error")))))

Unfortunately I now get the error: INCORRECT ARGUMENT SET

Have a clue what this means or is referring to so would love some guidance please

 

PDKing

If I have parent sections such as, work in process, ready to be started, bill of materials, in production... Can I use the RYG balls to organize those sections? For example, can i put work in process Green and ready to be started yellow, and when a project goes from ready to be started to work in process and I change the color of the ball. will it put that row into the necessary parent column??? 

J. Craig Williams

Patrick,

I reordered your formula

=IF([% Complete]10 = 1, "Completed", IF([End Date]10 > TODAY(), "Missed", IF([% Complete]10 > 0, "In Progress", "Not Started")))

The only question I have is you is that when the End Date is reached (but not passed) is it Missed or In Progress / Not Started ?

My formula above assumes it is not missed until the following day.

Craig

tsinghal

I would like to link RYG with Status, so if the status is "Completed", the RYG will show green; yellow for "In Progress, and red for "Not Started." Can you please assist with the formula?

Thanks,

T

J. Craig Williams

Try this:

=IF(Status23 = "Not Started", "Red", IF(Status23 = "Completed", "Green", IF(Status23 = "In Progress", "Yellow")))

Craig

Nicolle

Looking to do something along the lines of:

If Due Date ISBLANK RYG "Red", IF NOT ISBLANK "Yellow" If checked complete "Green"

I have date dependencies so I can't address this in the date column.

Please help.

THANK YOU!!!