Turnaround Time (TAT) Calculations — Reference Guide

This page documents the diff_seconds() expressions used to calculate elapsed time between key case milestones. Each formula returns the result in seconds, with optional conversions to hours/days shown below each one.

Adjust the field names below to match your actual schema if they differ.

Prerequisite: Fields Must Be Added to the Looker Table

Before any of these calculations will run correctly, each date/time field referenced below must first be added to the table in Looker. If a field isn't present in the table, the expression referencing it will fail or return an error, even if the syntax is correct.

Double-check that all of the following fields exist on the table before building these calculations:

• date_received_time
• date_accessioned_time
• date_grossed_time
• date_finalized_time

1. Received → Accessioned

What it does: Measures the elapsed time between when a case is received and when it is accessioned.

How it works: diff_seconds() subtracts the first timestamp from the second, returning the difference in seconds.

Build instructions:

1. In your Explore, add a new Custom Dimension (Table Calculation).
2. Paste the seconds formula below, adjusting field names if your schema differs.
3. Add a second calc for the hours or days conversion if a friendlier display is needed.

Formula (seconds):

diff_seconds(${np_case.date_received_time}, ${np_case.date_accessioned_time})

Formula (hours):

diff_seconds(${np_case.date_received_time}, ${np_case.date_accessioned_time}) / 3600

Formula (days):

diff_seconds(${np_case.date_received_time}, ${np_case.date_accessioned_time}) / 86400

Worked example: If a case is received at 08:00:00 and accessioned at 10:30:00, the calculation returns 9000 seconds — 2.5 hours.

Known gotchas: Make sure both fields are pulled into the query as raw datetime fields, not formatted display fields. A missing closing parenthesis is the most common cause of an "Expression incomplete" error.

2. Accessioned → Grossed

What it does: Measures the elapsed time between accessioning and grossing.

Build instructions:

1. Add a new Custom Dimension.
2. Paste the seconds formula below, then add an hours or days conversion calc if needed.

Formula (seconds):

diff_seconds(${np_case.date_accessioned_time}, ${np_case.date_grossed_time})

Formula (hours):

diff_seconds(${np_case.date_accessioned_time}, ${np_case.date_grossed_time}) / 3600

Formula (days):

diff_seconds(${np_case.date_accessioned_time}, ${np_case.date_grossed_time}) / 86400

Worked example: If a case is accessioned at 09:00:00 and grossed at 13:15:00, the calculation returns 15300 seconds — 4.25 hours.

Known gotchas: Same as above — verify raw datetime fields are used and parentheses are balanced.

3. Accessioned → Finalized (Total Case TAT)

What it does: Measures the total elapsed time from accessioning through case finalization — the broadest TAT metric in this set.

Build instructions:

1. Add a new Custom Dimension.
2. Paste the seconds formula below, then add hours/days conversions as needed.

3. If some cases haven't been finalized yet, wrap the calc in a null check so in-progress cases don't error out:

   if(${np_case.date_finalized_time} != null,  diff_seconds(${np_case.date_accessioned_time}, ${np_case.date_finalized_time}) / 3600,  null)
   

Formula (seconds):

diff_seconds(${np_case.date_accessioned_time}, ${np_case.date_finalized_time})

Formula (hours):

diff_seconds(${np_case.date_accessioned_time}, ${np_case.date_finalized_time}) / 3600

Formula (days):

diff_seconds(${np_case.date_accessioned_time}, ${np_case.date_finalized_time}) / 86400

Worked example: If a case is accessioned on Monday at 09:00:00 and finalized on Wednesday at 09:00:00, the calculation returns 172800 seconds — 2 full days.

Known gotchas: This is the total case TAT, so it will always be ≥ the sum of the individual stage TATs (received→accessioned, accessioned→grossed, etc.). If it isn't, check for null or out-of-order timestamps.

General Notes

• diff_seconds(start_date, end_date) always subtracts start_date from end_date — if fields are out of order, the result will be negative.
• Every expression must have matching open/close parentheses — an "Expression incomplete" error usually means one is missing.
• (Syntax for null checks may vary — confirm against your platform's conditional function reference.)