Dates - Due, Complete and Overdue based on Completion Date
I have a due date and completion date columns in my Smart Sheet and need help with formula to give responses based on dates. I have attached example for reference. For column with completion date being blank, would like to show "Due", for completion date before due date to show "Complete" and completion date past due date to show "Overdue". This is formula I put together after looking on this community, but seems to still not work. Help please!
=IF(AND([Due Date]@row > [Completion Date]@row, [Completion Date]@row > "0"), "Complete", IF(AND([Due Date]@row < [Completion Date]@row, [Completion Date]@row > "0"), "Overdue", IF(AND([Due Date]@row >= TODAY(), [Completion Date]@row = "0"), "Due")))
Best Answer
-
Try this rendition: It will check if the completion date is blank and show due. If that is false, Then it will check if the completion date is less then the due date, and show Complete. IF not, it will show Overdue.
=IF(AND(Today() < [Due Date]@row, Isblank([Completion Date]@row)), "Due", IF([Completion Date]@row <= [Due Date]@row, "Complete", "Overdue"))
Answers
-
Deleted and combined below.
-
Try this rendition: It will check if the completion date is blank and show due. If that is false, Then it will check if the completion date is less then the due date, and show Complete. IF not, it will show Overdue.
=IF(AND(Today() < [Due Date]@row, Isblank([Completion Date]@row)), "Due", IF([Completion Date]@row <= [Due Date]@row, "Complete", "Overdue"))
-
Mike,
Thanks for the help. It works perfectly now. Looking at what you have simplifies it. Realized there were too many arguments in my formula to work.
-
Not a problem. Glad I can help you out. There are so many different ways to tackle a formula. Some are sleeker than others. :)
-
Mike,
So the issue I'm having with it now is if "Due date" passes todays date the formula shows as complete. Working through this with your suggestions. Let me know if you have any ideas.
-
If you remove the Today() <= Due date part and just say, if the completion date is blank, then its due.
=IF(Isblank([Completion Date]@row), "Due", IF([Completion Date]@row <= [Due Date]@row, "Complete", "Overdue"))
Does that work?
-
Mike,
It's still not working the way I need it to. Not a big deal though, the formulas work correctly when entered in excel. I need to pull multiple sets of data from this smart sheet, so set up excel template to draw out the information needed when exporting the sheet.
Thanks for giving me a hand with this.
-
Can you share what formula you are using in Excel? I am confident we can get this working for you in smartsheet. I think I am just lacking the context needed. :) If not, that's cool. Glad you have something that works for you.
-
No problem. I made 2 separate formulas. The first would give answer for "due", "complete" and "overdue" based on entries. The second formula was just for "overdue" with reference to today's date. Couldn't get them to mesh and using as reference information in SUMIFS and COUNTIFS data collection. I'm sure if I continued to tinker, could get it to sync, but have reports to get in and was easiest way to automate it.
H7 - Due Date
I7 - Completion Date,
1st) =IF(AND(H7>=I7,I7>0),"Complete",IF(AND(H7<I7,I7>0),"Overdue",IF(AND(H7>=TODAY(),I7=0),"Due")))
2nd) =IF(AND(H7<TODAY(),H7>0,ISBLANK(I7)),"Overdue","N/A")
-
@Mike Wilday Hey Mike, I have a similar problem but I just want to work out something being "Overdue" using one date to compare against Today. This is not working though:
=IF(AND(TODAY() > [Tactical Due for Approval]@row, "OVERDUE"))
-
You don't need to use AND. IF(Today() [Tactical Due for Approval]@row, "Overdue") is all you need.
-
Hello, not sure if I'm missing something here but if the completion is date blank but the date is passed, my return is showing as due, not overdue.
Basically the due date has passed, they havent completed so there is not date, its showing as complete.
We actually want to ask 4 things:
- If completion date is same as or before due date return "Completed"
- If Completion date after due date return "Overdue"
- If no completion date and todays date after Due date return "Overdue"
- If no completion date and todays date before Due date return "Due"
-
Hi @Whibley
Try a structure like the following:
=IF(AND([Completion Date]@row <> "", [Completion Date]@row <= [Due Date]@row), "Completed", IF(OR([Completion Date]@row > [Due Date]@row, AND([Completion Date]@row = "", [Due Date]@row < TODAY())), "Overdue", IF(AND([Completion Date]@row = "", [Due Date]@row >= TODAY()), "Due")))
Cheers,
Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 67 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!
