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

Answers

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

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

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

  • 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
    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
    Mike Wilday ✭✭✭✭✭✭

    @VicFar

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

  • Whibley
    Whibley
    edited 02/02/24

    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"


  • 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!