Deadline Formula displays Past Due when dates are blank
Hello,
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
-
Hi @CK17
I hope you're well and safe!
Try something like this.
=IF(OR([Start Date]@row = "", [Due Date]@row = ""), "", your formula
Did that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi @CK17
I hope you're well and safe!
Try something like this.
=IF(OR([Start Date]@row = "", [Due Date]@row = ""), "", your formula
Did that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you! That worked! At first I had an issue copy pasting but that was because I was pasting for row 5 everywhere. Realized that by updating everything to @row is better.
-
Excellent!
Yes, @row is best practice because it's more efficient, and you can also convert it to a column formula
You're more than welcome!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Done and done on @row and column formula.
Thanks again, have a great weekend!
-
I'm always happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!