# Dates - Due, Complete and Overdue based on Completion Date

Options

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

• ✭✭✭✭✭✭
edited 11/30/20
Options

Deleted and combined below.

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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?

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• edited 02/02/24
Options

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"

• Employee
Options

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