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

• ✭✭✭

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:

• ✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!