Formula help!
=IF([Eval Date Scheduled]@row <= TODAY(), IF([Employee Eval Status]@row = 1, "Green"), IF([Eval Date Scheduled]@row < TODAY(), IF([Employee Eval Status]@row = 0, "Yellow"), IF(ISBLANK([Eval Date Scheduled]@row), "", IF([Employee Eval Status]@row <> 0, "", "Red"))))
I am trying to create a formula based on If a date is reached and a check box is checked return green, if date is reached and check box is unchecked return yellow, if date is in the past and checkbox is unchecked return red, if date is empty and checkbox is unchecked leave blank.
Best Answer
-
I think this is correct now and a bit simpler:
=IFERROR(IF(ISBLANK([Eval Date Scheduled]@row), "", IF([Employee Eval Status]@row = 1, "Green", IF([Eval Date Scheduled]@row = TODAY(), "Yellow", IF([Eval Date Scheduled]@row < TODAY(), "Red", "")))), "")
Here is the expected results based on the calculation above:
Answers
-
Try something like this:
=IFERROR(IF(AND(ISBLANK([Eval Date Scheduled]@row), [Employee Eval Status]@row <> 0), "Red", IF(AND([Eval Date Scheduled]@row <> "", [Eval Date Scheduled]@row <= TODAY()), IF([Employee Eval Status]@row = 1, "Green", "Yellow"), "")), "")
-
It is working for the most part, however if a Date is in the past and the checkbox is uncheck it is not turning red, it is yellow.
-
Apologies - I see now I misread one aspect of your intended outcome.
Just so I am clear can you confirm what the return values should be below and correct any inaccuracies:
-
Everything is correct except as long as the Date Timing is Blank, even if the checkbox is checked it will return no value
-
I think this is correct now and a bit simpler:
=IFERROR(IF(ISBLANK([Eval Date Scheduled]@row), "", IF([Employee Eval Status]@row = 1, "Green", IF([Eval Date Scheduled]@row = TODAY(), "Yellow", IF([Eval Date Scheduled]@row < TODAY(), "Red", "")))), "")
Here is the expected results based on the calculation above:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!