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

  • Matt Small
    Matt Small ✭✭✭
    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

  • Matt Small
    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
    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
    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
    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
    Matt Small ✭✭✭
    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:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!