Automate RYGGrey

I need help with a formula to automate my RYGGrey. Here are the rules:

If status = complete, grey

If status is in progress or not started and finish is in the past, red

If status is in progress or not started and finish is today + 5 days (within the next 5 days), yellow

If status is in progress or not started and finish is greater than 5 days out, green

Find more posts tagged with

Healthcare

Formulas

Give this a go...

=IF(Status@row = "Complete", "Gray", IF(Finish@row < TODAY(), "Red", IF(Finish@row < TODAY(5), "Yellow", "Green")))

INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…
Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!