If(AND(OR statement for Project health based on status
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?
Best Answer
-
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", ""))))))))))
Answers
-
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", ""))))))))))
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Devin Lee This is what I was looking for. I did not mean to hit No.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!