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

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!

Hi @Jeff Reisman  thanks for the thoughts. I will try this. The automation rules are a little easier to construct for us formulachallenged people.
Will let you know how it works!
susan
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!