Calculate Sample Turnaround with a UDR

Knowing the sample turnaround time for specific samples processed within your lab can be a valuable managerial tool. This article shows you how to use sample dates and times and a user-defined report (UDR) to calculate this important metric. While you can export sample data to an Excel template to perform similar calculations, a simple UDR works well.

Begin by selecting the Define Report option on the Samples menu to create a new UDR. Enter a report name and optional sort order, select an appropriate template from the list then select the Columns tab to add the UDR’s columns.

You can determine a sample’s turnaround time using date arithmetic and any of the sample’s date and time values. For example, you may define turnaround as the time elapsed between sample login and sample completion. If you are using the reported date and time feature, you may use reported date to calculate elapsed time. For our example we will use received date and time through reported date and time to calculate turnaround time.

In addition to calculating and displaying turnaround time, we want our UDR to display the dates and times on which the calculation is based. Add columns A and B to the UDR to display received date and time and reported date and time, respectively. Enter an appropriate column label then select the “Received date and time” and “Reported date and time” Visual Basic for Applications (VBA) expression from the column’s Expression pick list.

VBA’s DateDiff function is an ideal solution for our turnaround calculation. You can use DateDiff to determine the difference (i.e. the number of time intervals) between two dates, times, or dates and times in seconds, minutes, days, weeks, etc. Below are a few examples. Note that like an Excel formula VBA expressions in a UDR column begin with an equal sign. See Table 1, below.

Instead of the literal times and dates in the examples above, we simply use the appropriate LIMS field name in the UDR column expression. Before we create our expression we should review the available LIMS date and time fields. Table 2 shows the date and time fields available for each sample. A sample’s analyses have their own dates and times, which are not listed here. Note that the fields whose values are auto[1]matically assigned by the LIMS contain both a date and a time. All other sample fields contain either a date or a time and they may have no data for optional fields.

To calculate the number of hours elapsed between the sample’s login date and time and completed date we can use this expression:

The expression above is simple because both the AddedDate and CompletedDate fields include both a date and a time. What if we want to calculate the difference between received date and time and reported date? The serial values Microsoft Access uses for dates and times allow us to simply add the separate date and time values to create a single date and time value. To calculate the number of hours elapsed between the sample’s received date and time and reported date we can use this expression:

The expression above works properly if you always enter a received time with the received date when logging samples. If you sometimes omit the received time we need to update our expression to insert a default time value when the received time is blank. We can use VBA’s Nz function to provide a default time (midnight in our example) when there is no received time:

Add the expression above to column C in the UDR with an appropriate column label.

Since we would also like to see the average turnaround for the samples queried for the report, simply enable the Average summary statistic for column C.

The UDR definition in the screen capture, below left, shows a sample’s turnaround in hours but that may not be intuitive when turnarounds are measured in days. Can we display each sample’s turnaround in days and hours? Here’s an expression that will work:

This expression uses the DateDiff function twice. The result of the first DateDiff is divided by 24 using the backslash integer division operator to return a whole number of days (e.g. 60 \ 24 returns 2). The result of the second DateDiff is used with the Mod operator to return the remainder of the division (e.g. 60 Mod 24 returns 12). The results are concatenated with literal text to produce the desired output. If DateDiff returns 60 hours, the expression above produces the text string “2 days 12 hrs”.

With our turnaround now in days and hours you will notice that the column’s average is only averaging the days. This is to be expected since we are asking our report to average text strings in the form “2 days 12 hrs”. Only the first number is used when calculating the average.

If we really want our turnaround average in the same form, we can calculate and display the average using our own expression in the UDR’s report Text field located on the Headers and Footers tab. Here’s a report text expression to calculate the average turnaround in days and hours:

The UDR report text field is often used to display disclaimers following the report’s data. As shown above, it can also include VBA expressions to calculate and display special statistics or even cross-column calculations that can not be accomplished using the default column statistics. With a few VBA expressions, UDRs in LIMS offer a powerful solution.