Formula Help: Need to show Blank cell as Late if Date in Adjacent Cell is Past

Hi all,

I think this should simple for a formula guru, but I'm not and I've inherited sheets with formulas from a former colleague and trying to make them work as expected.

The sheet has two columns we need to evaluation: Due Date and Date Completed. In a helper column, we have this formula:

=IF([Date Completed]@row <= [Due Date]@row, "On Time", "Late")

Which works great when both Due Date and Dated Completed have values. But, if the Date Completed is blank, the formula says it is On Time. That is correct for future dates, but not past dates.

So, I need a formula that tells me something is late when the Due Date has passed and the Date Completed is blank. Do I need to use a nested IF formula?

Here is a screenshot of the results with the formula above:


Help appreciated!

Thanks,

susan

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/20/22

    Perhaps using an automation rule in conjunction with a helper column with a formula might work better here instead of only a formula?

    Add a helper column called "Late" and use it to determine if the Completed Date came after the Due Date, or vice versa:

    =IF([Completed Date]@row > [Due Date]@row, "Late", IF([Due Date]@row >= [Completed Date]@row, "On Time", ""))

    We will use this helper column value in the automation rule:

    Trigger: Run once a day

    Condition Path 1: If Due Date is in the past, AND if Completed Date is blank

    Action 1: Change cell value in Status column to "Late"

    Add additional condition paths:

    Condition Path 2: If Due Date is not in the past, AND Completed Date is blank

    Action 2: Clear cell value in Status column

    Condition Path 3: If Late equals "Late"

    Action 3: Change cell value in Status column to "Late"

    Condition Path 4: If Late column equals "On Time"

    Action 4: Change cell value in Status column to "On Time"

    Your automation rule will look something like this:


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Susan Peck
    Susan Peck ✭✭✭✭✭✭

    Hi @Jeff Reisman - thanks for the thoughts. I will try this. The automation rules are a little easier to construct for us formula-challenged people.

    Will let you know how it works!

    susan

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!