Calculate Overdue Documents

Anil Rattan

I have two dates columns. One is Target Completion Date and the other New Target Date.

I have the following two questions:

1. I wanted to know how many documents did not meet the Target Completion date. Total counts.
2. If the Target Completion Date was extended then how many documents missed the New Target Dates. Total counts.

Thanks!

Paul Newcome

Are you currently tracking the Actual Completion Date?

Anil Rattan

Yes, I am tracking both the dates.

Andrée Starå

Hi @Anil Rattan

Have a look at my post below with a method I developed that I think could help with your need.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

If you are tracking the Actual Completion Date, then you should be able to insert two helper columns (that can later be hidden).

In one helper column use:

=IF([Actual Completion Date]@row > [Target Completion Date]@row, 1)

And in the other helper column use:

=IF([Actual Completion Date]@row > [New Target Completion Date]@row, 1)

Then you can use a SUM on each of the columns to tell you how many are past target and how many are past new target.