Health Formula

Nick J

Hello, I'm trying to create a health formula based on the below conditions, but I keep getting an #UNPARSABLE error. Hopefully someone in the community can assist.

RED: If the Expected Project Go Live Date is less than 30 days from today AND the Project Phase is not "Go-Live" or "Complete"

YELLOW: If the Expected Project Go Live Date is less than 60 days from today AND the Project Phase is not "Testing", "Go-Live", or "Complete"

GREEN: If the above conditions are not met OR Project Phase is "Complete"

Frank B.

Hi @Nick J try this:

=IF(

 AND(

  [Expected Project Go Live Date]@row - TODAY() < 30,

  NOT(OR(

   [Project Phase]@row = "Go-Live",

   [Project Phase]@row = "Complete"

  ))

 ),

 "RED",

 IF(

  AND(

   [Expected Project Go Live Date]@row - TODAY() < 60,

   NOT(OR(

    [Project Phase]@row = "Testing",

    [Project Phase]@row = "Go-Live",

    [Project Phase]@row = "Complete"

   ))

  ),

  "YELLOW",

  "GREEN"

 )

)

Here's how the formula works:

1. The first condition checks if the [Expected Project Go Live Date] is less than 30 days from today ([Expected Project Go Live Date]@row - TODAY() < 30). It also verifies that the [Project Phase] is not "Go-Live" or "Complete" (NOT(OR(...))). If both conditions are met, the status is set to "RED".
2. If the first condition is not met, the formula moves to the next condition. It checks if the [Expected Project Go Live Date] is less than 60 days from today ([Expected Project Go Live Date]@row - TODAY() < 60). It also ensures that the [Project Phase] is not "Testing", "Go-Live", or "Complete" (NOT(OR(...))). If both conditions are met, the status is set to "YELLOW".
3. If neither of the above conditions is met, the formula defaults to "GREEN". This condition covers situations where the specified conditions are not met or if the [Project Phase] is "Complete".

Adjust the column references and status values as necessary to match your specific Smartsheet setup and requirements.

HTH!

Frank B.

Hi @Nick J try this:

=IF(

 AND(

  [Expected Project Go Live Date]@row - TODAY() < 30,

  NOT(OR(

   [Project Phase]@row = "Go-Live",

   [Project Phase]@row = "Complete"

  ))

 ),

 "RED",

 IF(

  AND(

   [Expected Project Go Live Date]@row - TODAY() < 60,

   NOT(OR(

    [Project Phase]@row = "Testing",

    [Project Phase]@row = "Go-Live",

    [Project Phase]@row = "Complete"

   ))

  ),

  "YELLOW",

  "GREEN"

 )

)

Here's how the formula works:

1. The first condition checks if the [Expected Project Go Live Date] is less than 30 days from today ([Expected Project Go Live Date]@row - TODAY() < 30). It also verifies that the [Project Phase] is not "Go-Live" or "Complete" (NOT(OR(...))). If both conditions are met, the status is set to "RED".
2. If the first condition is not met, the formula moves to the next condition. It checks if the [Expected Project Go Live Date] is less than 60 days from today ([Expected Project Go Live Date]@row - TODAY() < 60). It also ensures that the [Project Phase] is not "Testing", "Go-Live", or "Complete" (NOT(OR(...))). If both conditions are met, the status is set to "YELLOW".
3. If neither of the above conditions is met, the formula defaults to "GREEN". This condition covers situations where the specified conditions are not met or if the [Project Phase] is "Complete".

Adjust the column references and status values as necessary to match your specific Smartsheet setup and requirements.

HTH!

Nick J

Thank you @Frank B. !!!