If(AND(OR statement for Project health based on status

RavenM

Need to know:

Health - green, yellow, red

Statuses - Defining Requirements, In Review, Reviewed, Validated

There are dates that we want the project to be in the Reviewed status by. I'm trying to write a script that will allow me to set a "red", "yellow", or "green" health score based on the status of the program and the date. For instance, if the date is the date in the Reviewed status column, but the status is In Review or Defining Requirements, I was the health column to show red. If the date is a week away, but the status is In Review, the health should be yellow. If the Reviewed status is reached before the date, the health should be green. Can someone help me with this?

Devin Lee

You have some gaps in your logic. Think about each status separately and what range of dates would cause each color. Below would be an example

Defining Requirements: Green for greater than 14 days, Yellow between 7-14 days, and Red for less than 7

In Review: Green for greater than 7 days, Yellow between 7-0 days, and Red for less than 0

Reviewed: Green for greater than 7 days, Yellow between 7-0 days, and Red for less than 0

Validated: Green for all

For this example the formula would look like this:

=IF(Status@row = "Validated", "Green", IF(AND(Status@row = "Defining Requirements", Date@row >= TODAY(14)), "Green", IF(AND(Status@row = "Defining Requirements", AND(Date@row < TODAY(14), Date@row > TODAY(0))), "Yellow", IF(AND(Status@row = "Defining Requirements", Date@row <= TODAY()), "Red", IF(AND(Status@row = "In Review", Date@row >= TODAY(7)), "Green", IF(AND(Status@row = "In Review", AND(Date@row < TODAY(7), Date@row > TODAY(0))), "Yellow", IF(AND(Status@row = "In Review", Date@row <= TODAY()), "Red", IF(AND(Status@row = "Reviewed", Date@row >= TODAY(7)), "Green", IF(AND(Status@row = "Reviewed", AND(Date@row < TODAY(7), Date@row > TODAY(0))), "Yellow", IF(AND(Status@row = "Reviewed", Date@row <= TODAY()), "Red", ""))))))))))

Devin Lee

You have some gaps in your logic. Think about each status separately and what range of dates would cause each color. Below would be an example

Defining Requirements: Green for greater than 14 days, Yellow between 7-14 days, and Red for less than 7

In Review: Green for greater than 7 days, Yellow between 7-0 days, and Red for less than 0

Reviewed: Green for greater than 7 days, Yellow between 7-0 days, and Red for less than 0

Validated: Green for all

For this example the formula would look like this:

=IF(Status@row = "Validated", "Green", IF(AND(Status@row = "Defining Requirements", Date@row >= TODAY(14)), "Green", IF(AND(Status@row = "Defining Requirements", AND(Date@row < TODAY(14), Date@row > TODAY(0))), "Yellow", IF(AND(Status@row = "Defining Requirements", Date@row <= TODAY()), "Red", IF(AND(Status@row = "In Review", Date@row >= TODAY(7)), "Green", IF(AND(Status@row = "In Review", AND(Date@row < TODAY(7), Date@row > TODAY(0))), "Yellow", IF(AND(Status@row = "In Review", Date@row <= TODAY()), "Red", IF(AND(Status@row = "Reviewed", Date@row >= TODAY(7)), "Green", IF(AND(Status@row = "Reviewed", AND(Date@row < TODAY(7), Date@row > TODAY(0))), "Yellow", IF(AND(Status@row = "Reviewed", Date@row <= TODAY()), "Red", ""))))))))))

Genevieve P.

Hi @RavenM

I see that you marked Devin's response as not answering your question. Can you identify what it was about the formula that didn't work for you, or what further questions you have?

Cheers,

Genevieve

RavenM

@Devin Lee This is what I was looking for. I did not mean to hit No.