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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!