Dates - Due, Complete and Overdue based on Completion Date

11/30/20
Accepted

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

Answers

  • Mike WildayMike Wilday ✭✭✭✭✭
    edited 11/30/20

    Deleted and combined below.

  • 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 WildayMike 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. :)

  • 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 WildayMike 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?

  • 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 WildayMike 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.

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

Sign In or Register to comment.