How do I stop counting overdue days when the completion date is met using my formula below?

I have column headers (that reference my formula, conditional formatting, and workflows) for: assigned start date, assigned due date, projected duration, days remaining, overdue, start date, completed date, actual duration, and done. My formula to count days "overdue" is below:

=IF(Done@row <> "Complete", IF(TODAY() - [Assigned Due Date]@row >= 0, TODAY() - [Assigned Due Date]@row))

It works, but I want it to stop counting overdue when the "completed date" is entered. The "done" column is a checkbox and when checked it runs an automation to archive the entire task to another sheet. Sometimes I don't want to archive the task even though it's completed; therefore, because the way the formula is written it continues to count the task as overdue even though it's technically complete.




  • Sorry, I realized I left a key component out of the above snippet, see this one below. This is the actual task I want to correct the "overdue" because technically it was completed same day as the assigned due date. The "complete" is referenced from the "status" column in my sheet....if this helps any!

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 01/29/21

    Hi Jeremy

    This formula isn't quite right. You are saying here that if the Value in the Done column on the current row is not set to the value "Complete" then... but the Done column is a checkbox which will only ever contain a 1 or 0, so this first clause is sort of obsolete...

    =IF(Done@row <> "Complete", IF(TODAY() - [Assigned Due Date]@row >= 0, TODAY() - [Assigned Due Date]@row))

    I think you'll need

    =IF(Status@row="Complete","",TODAY() - [Assigned Due Date]@row)

    This will look at the status, if it is complete then nothing will enter into the Overdue days. If the status is NOT complete then it will calculate todays date - assigned due date.

    Is this what you want?


    You could use:

    =IF(ISDATE([Completed Date]@row),"",TODAY() - [Assigned Due Date]@row)

    This one means if there is a date in complete date then don't do anything in overdue days, otherwise put in the number of days between today and assigned due date.

    Kind regards


  • Jeremy Logan
    edited 01/29/21

    Thanks Debbie, the second formula is more what I was looking for except it inputs a negative number in the overdue column which I didn't want. The formula I had before did not input a value in overdue until the countdown got to zero, then it started counting positive numbers continuously.

    Amended: Debbie,

    I used the first formula, changed the manually entered completed date to an automated workflow to record the date when completed is changed in the status column. I tried to run a conditional format to hide the negative numbers in the overdue column via white font on a white background but it wont recognize "less than 0"....

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Ahh, ok have you tried:

    =IF(ISDATE([Completed Date]@row),"",IF(TODAY() - [Assigned Due Date]@row >= 0, TODAY() - [Assigned Due Date]@row))

    Oh have I just got confused! 😂

  • Debbie,

    this is my current formula for Overdue:

    =IF(ISDATE([Completed Date]@row), "", TODAY() - [Assigned Due Date]@row)

    I changed the other conditions in the Smartsheet to simplify the process; thanks for your help getting there!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!