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

Options

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

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    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

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓
    Options

    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.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • RavenM
    RavenM ✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!