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

Whibley

Hello, with regards to negative numbers, my formula is similar to above but based on the due date:

=IF(ISBLANK(DueDate),"no due date",TODAY() - DueDate)

Future dates are returning negative figures. How do I ask that to return "Due" rather than a minus figure?

Thanks in advance :)

Debbie Sawyer

Hi @Whibley

I'm not 100% sure I follow the question but if you change the false statement from TODAY() - DueDate to the Text entry "Due" then you will get the word instead of the figure...

=IF(ISBLANK(DueDate@row),"no due date","Due")

Is this what you mean?

Or you could do something like:

=IF(ISBLANK(DueDate@row),"no due date",IF(DueDate@row <TODAY(),"Overdue", IF(DueDate@row=Today(),"Due Today","Not due yet")))

Or if you wanted to only return Due Date data if the task is not yet complete then you could say something like

=IF([% Complete]@row = 1,"Complete",IF(ISBLANK(DueDate@row),"no due date",IF(DueDate@row <TODAY(),"Overdue", IF(DueDate@row=Today(),"Due Today","Not due yet"))))

Kind regards

Debbie