Report # of days between uploaded document and planned completion date.

[dawnmaried10]

Hello all, I am trying to calculate the number of days between the most recent uploaded document to the planned completion date of a request.

Example: document uploaded 02.05.21 - planned completion date 02.09.21 = 4 days (Days or Hours would work.)

We are trying to identify when a request is made vs. when a document is uploaded to complete the request. Often a request is made weeks in advance and the planned completion date is scheduled but the necessary documents are not provided or updated until just before the completion date. We are looking to identify how often this happens and if there is a common theme with specific requestors/groups. Because of the volume of requests it would be beneficial to report how frequently this happens.

I'm not great at text mode or calculated data expressions but know a lot of you out there are. I've tried everything I could think to no avail. any help would be appreciated.

=D

[NicholeVargas]

@Dawnmarie DeAngelo‚

You'll want to use a Document Report to capture this information. The text mode below will calculate the duration between the Current Document Version Entry Date and the Request Planned Completion Date, based on the system's default schedule - so it will skip over both weekends and schedule exceptions, such as company holidays. The WORKMINUTESDIFF function gives a value in minutes, so that is why the calculation is divided by 480 (number of minutes in an 8-hour day) to convert that value to days.

To add this to your Document Report, add a new Column, switch to text mode and paste the following:

displayname=# of Days Between Document Entry Date and Request Planned Completion Date

textmode=true

valueexpression=ROUND(DIV(WORKMINUTESDIFF({currentVersion}.{entryDate},{opTask}.{plannedCompletionDate}),480),2)

valueformat=HTML

NOTE: Columns that are built using text mode cannot be used in charts or graphs. If you would like to chart by this data, you would want to create a calculated data expression. The blog, Tracking Turnaround Time, provides a few examples of how to do this - you would just need to swap the fields based on your ask.

If you have any questions or get stuck, let me know!

[dawnmaried10 · Accepted solution]

Hi Nichole,

This is perfect. Wanted to share I am using this in an Issue report. My text mode looks like this:

valueexpression=ROUND(DIV(WORKMINUTESDIFF({currentVersion}.{entryDate},{opTask}.{plannedCompletionDate}),480),2)

listdelimiter=<br>

listmethod=nested(documents).lists

valueformat=HTML

displayname=Doc Uploaded vs. Comp. Date

textmode=true

type=iterate

Thank you again,

Dawnmarie