Modifying RYG Symbols with Dates and Status

Jose Munoz

We are trying to create a formula on the Schedule field that will change color to Red, Green or Yellow based on Multiple day and Status selection. 

For example:

If Status is In Progress and End Date is > Today and R - Date is Empty then is Green.

IF Status is In Progress and End Date is < Today and R - Date is Empty or a Date > than End Date or Today Green. 

IF Status is In Progress and End Date is < Today and R - Date is Empty is also < Today() then "Red"

=IF(Status3 = "In Progress", IF([End Date]3 < TODAY(), IF(AND([R - Date]3 > TODAY(),, "Red", IF([End Date]3 = TODAY(), "Yellow", IF([End Date]3 > TODAY(), "Green"))))

Will this be possible? 

Also is there a way to make the formula hard coded on the field so nobody can delete it and if new records get added the calculation remains in the new rows? 

Thanks. 

Paul Newcome

I am having a little trouble following your criteria...

.

If Status is In Progress and End Date is > Today and R - Date is Empty then is Green.

This one makes sense.

.

IF Status is In Progress and End Date is < Today and R - Date is Empty or a Date > than End Date or Today Green.

Not too sure what you mean by the bold portion of this one.

. 

IF Status is In Progress and End Date is < Today and R - Date is Empty is also < Today() then "Red"

Not sure on the bold portion of this one either.

.

=IF(Status3 = "In Progress", IF([End Date]3 < TODAY(), IF(AND([R - Date]3 > TODAY(),, "Red", IF([End Date]3 = TODAY(), "Yellow", IF([End Date]3 > TODAY(), "Green"))))

Your criteria list did not mention anything about "Yellow". Can you specify what you are looking for to generate that one?

Jose Munoz

IF Status is In Progress , End Date is < Today and R - Date is Empty or >Today Green. ( i hope this clarifies, the thing is that we want to turn RED if the End Date is past Due so they can enter then a Revised Date (R - Date) when they enter a Revised date that is < Today will turn GREEN but if the Revised Date also Expires > Today then will turn RED. 

Yellow if is getting closer to the End Date or Revise Date, will this make sense? 

I tried for a week to create the formula and is killing me, Im going nowhere. 

Thanks for the help!!!

Paul Newcome

Ok. That helps. Lets see if we can really drill down on the details of the criteria. We also need to make sure it is in the right order according to priority.

.

If the Status is in Progress --> This is consistent throughout, so we will ignore it for now instead of repeating it every time.

.

"Yellow if is getting closer to the End Date or Revise Date"

This part will need a little more detail in regards to the specific criteria. What exactly is "getting closer"? Tomorrow? Next week? Next month? Other?

.

Basically what we are saying is that if there is a Revised Date, use it, otherwise use the End Date.

.

Red would be if the date is past due.

Green would be for if the date is in the future.

We just need to establish your Yellow criteria, and we will be able to start writing out your formula.

Jose Munoz

Yellow will be 5 days before the End Date or Revise Date to warn them. 

Regarding the Revise date will be Empty until End Date is Past Due in order to bring the project back to Green they will need to enter a revised date in the future but if this date also expires will turn RED again. 

Will this help? Please let me know if you need anything else. 

the other values for Status are, but we only care for In Progress 

Canceled

Complete

In Progress

Not Started

On Hold

Paul Newcome

=IF(Status@row = "In Progress", IF(IF(ISDATE([R - Date]@row), [R - Date]@row, [End Date]@row) < TODAY(), "Red", IF(IF(ISDATE([R - Date]@row), [R - Date]@row, [End Date]@row) <= TODAY(5), "Yellow", "Green")), "")

.

Plug this in and see how it works for you with a variety of dates/date combinations.

Jose Munoz

It worked like a charm...WOW I appreciate very much your time and support. Lovely. 

If I decide to modify it in the future to play with other Statuses, will I just Copy and Paste the statement and replace the Status Value? 

Paul Newcome

"It worked like a charm...WOW I appreciate very much your time and support. Lovely." 

 

Happy to help! 

 

Sometimes spending that extra time on questions and clarification really pays off.

.

"If I decide to modify it in the future to play with other Statuses, will I just Copy and Paste the statement and replace the Status Value?"

 

Possibly yes and possibly no. It depends on how exactly you want everything to work overall.

 

I keep notifications turned on for threads I have commented on even after a working solution is found, so if you need anything further whether it be additional statuses or different criteria, so on and so forth, feel free to reply to this thread again. That way we will still have all of our previous notes for reference if we need them.

Jose Munoz

I will Thanks. Great work.