Three value RGB fomula?

J.Barrow

So I have a template that I have adapted and it had a base formula for project health based on the difference in the planned date and the actual date. The issue is that it does not link to todays date so if you dont change the actual date it looks green even if it is days late. On the flip side if something is way late, but then changed to complete it still shows red.

Current Formula: =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))

I wanted to add both todays date to the scheduled due date and the completion status of Canceled or Completed.

So I tried this formula with no luck: =IF((Status@row = "Canceled", "Green", IF(Status@row = "Complete", "Green", IF([End Date]@row - TODAY() < 0, "Red", IF([End Date]@row - TODAY() < =+5, "Yellow", "Green"), IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))))))))

Image for more context top two are new formulas the green are all the old formulas.

Any suggestions?

Mike Wilday

This functionality will update the sheet every day at 2pm and therefore update all your RGB's. You don't have to integrate the Today() formula to make that happen (HOWEVER I do see you are trying to calculate a date based on today's date. If you need help with the formula try adjusting it like this:

=IF(Status@row = "Canceled", "Green", IF(Status@row = "Complete", "Green", IF([End Date]@row - TODAY() < 0, "Red", IF([End Date]@row - TODAY() <= 5, "Yellow", IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))))))

I believe you had left in the end of the original formula which was messing it up. As well as the space between <=5 I also removed the +5 because 5 is the same thing.

I highlighted the issues as I found them below.

Mike Wilday

@Andrée Starå created a method for automating the update of the sheet. Here is his solution and this will daily update the status without using any third party tools.

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

J.Barrow

@Mike Wilday, I am not following how this functionality will fix my issue?

Mike Wilday

This functionality will update the sheet every day at 2pm and therefore update all your RGB's. You don't have to integrate the Today() formula to make that happen (HOWEVER I do see you are trying to calculate a date based on today's date. If you need help with the formula try adjusting it like this:

=IF(Status@row = "Canceled", "Green", IF(Status@row = "Complete", "Green", IF([End Date]@row - TODAY() < 0, "Red", IF([End Date]@row - TODAY() <= 5, "Yellow", IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))))))

I believe you had left in the end of the original formula which was messing it up. As well as the space between <=5 I also removed the +5 because 5 is the same thing.

I highlighted the issues as I found them below.