Health Formula
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"
Best Answer
-
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:
- 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". - 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". - 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!
- The first condition checks if the [Expected Project Go Live Date] is less than 30 days from today (
Answers
-
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:
- 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". - 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". - 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!
- The first condition checks if the [Expected Project Go Live Date] is less than 30 days from today (
-
Thank you @Frank B. !!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!