Formula help!

ldavies95

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

Matt Small

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:

Matt Small

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"), "")), "")

ldavies95

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.

Matt Small

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:

ldavies95

Everything is correct except as long as the Date Timing is Blank, even if the checkbox is checked it will return no value

Matt Small

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: