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

Susan Peck

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

Jeff Reisman

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: