Dates - Due, Complete and Overdue based on Completion Date

Nick Robertson

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")))

Mike Wilday

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 Wilday

Deleted and combined below.

Mike Wilday

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"))

Nick Robertson

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.

Mike Wilday

Not a problem. Glad I can help you out. There are so many different ways to tackle a formula. Some are sleeker than others. :)

Nick Robertson

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.

Mike Wilday

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?

Nick Robertson

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.

Mike Wilday

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.

Nick Robertson

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")

VicFar

@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"))

Mike Wilday

@VicFar

You don't need to use AND. IF(Today() [Tactical Due for Approval]@row, "Overdue") is all you need.

Whibley

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:

1. If completion date is same as or before due date return "Completed"
2. If Completion date after due date return "Overdue"
3. If no completion date and todays date after Due date return "Overdue"
4. If no completion date and todays date before Due date return "Due"

Genevieve P.

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