Modifying RYG Symbols with Dates and Status

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.

Find more posts tagged with

Formulas

Conditional Formatting

Project Management

Media & Entertainment

Enhancement Request

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?