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

Jeremy Logan

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.

Thanks,

Jeremy

Jeremy Logan

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

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?

OR

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

Debbie

Jeremy Logan

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

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! 😂

Jeremy Logan

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!

Jeremy