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.
Help Article Resources
Check out the Formula Handbook template!