IF ISBLANK, 3 options

Hi SmartSheet Community!

I am working on a formula to display closure status as "Late", "On time" and "Open". I want to show open if the "Actual Close Date" field is blank. If "Actual Close Date" is populated I need to compare against the "Expected Close Date" to determine if on time or late.

The following returns an error:

=IF(ISBLANK([Actual Close Date]@row, "Open", (IF((Status@row[Expected Close Date]@row > [Actual Close Date]@row, "Closed - On Time", "Closed - Late")

What updates do I need to make to ensure the closure status displays appropriately?

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓

    Hi @SMChou , you were on the right track!

    Try this one...

    =IF(ISBLANK([Actual Close Date]@row), "Open", IF([Expected Close Date]@row >= [Actual Close Date]@row, "Closed - On Time", "Closed - Late"))

    This one assumes that if the Expected and Actual dates are equal then it closed on time.

    Does that help?


    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!