Deadline Formula displays Past Due when dates are blank


I am using a fairly extended IFAND formula for determining task completion based on dates entered (Upcoming Deadline, Due Soon, Due Today, Past Due, Complete - Ahead, Complete - On Time, Complete - Late). When the dates are all blank, Past Due displays, which is not correct because the task hasn't even come up. Is there a way I can modify the formula so that if the start date and due dates are blank that deadline status is also blank or something that indicates no imminent action?

Formula: =IF(AND([Date Completed]5 = "", OR([Due Date]5 = TODAY(1), [Due Date]5 = TODAY(2), [Due Date]5 = TODAY(3))), "Due Soon", IF(AND([Date Completed]5 = "", [Due Date]5 = TODAY()), "Due Today", IF(AND([Date Completed]5 = "", TODAY() >= [Start Date]5, TODAY() <= [Due Date]5), "Upcoming Deadline", IF(AND([Date Completed]5 = "", [Due Date]5 > TODAY()), "", IF(AND([Date Completed]5 = "", [Due Date]5 < TODAY()), "Past Due", IF([Date Completed]5 < [Due Date]5, "Complete - Ahead", IF([Date Completed]5 = [Due Date]5, "Complete - On Time", IF([Date Completed]5 > [Due Date]5, "Complete - Late"))))))))

Example: Complete - Late and Past Due on the bottom portion are working as expected. On the top portion, it should not display. Due Date is not < Today because it is blank, but it shows Past Due regardless.

Thank you!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!