Help with calculating time difference using two criteria fields

[DSpence]

I have data regarding the response of units to a particular incident based on their "status change", I.E. Dispatched, Arrived, Transport, Available, and need to calculate Response time (Dispatch to arrive), and Total time (Dispatch to Available).

I am trying to calculate the time difference in a spreadsheet based on two sets of criteria. I have a field named Event# which has multiple entries depending on a Unit ID (which will also have multiple entries) and then a Unit Status.

The first criteria needs to evaluate Event# to compare only those Event #'s that are the same, and the second needs to evaluate Unit ID to then compare only Unit ID's are the same within that particular Event # (there can be multiple units for one Event #). I am looking to determine the time difference between two entries where the Date/Time field represents a particular Unit Status change.

My issue is how to do this in one or more steps.

Spreadsheet for help.xlsx

[TMS]

Is this what you want:

=LET(f, FILTER(K:K,(C:C="E2156719")*(F:F="344-5"),""), INDEX(f, ROWS(f))-INDEX(f, 1))

[DSpence]

Is this what you want:

=LET(f, FILTER(K:K,(C:C="E2156719")*(F:F="344-5"),""), INDEX(f, ROWS(f))-INDEX(f, 1))

It does work for total time (Dispatched to Available) but what about an intermediate time such as time from Dispatched to Arrived.

[TMS]

Maybe this:

= FILTER(K:K,(C:C="E2156719")*(F:F="344-5")*(H:H="Arrived")) - FILTER(K:K,(C:C="E2156719")*(F:F="344-5")*(H:H="Dispatched"))

[DSpence]

Maybe this:

= FILTER(K:K,(C:C="E2156719")*(F:F="344-5")*(H:H="Arrived")) - FILTER(K:K,(C:C="E2156719")*(F:F="344-5")*(H:H="Dispatched"))

That works great except for times that transition over midnight. Ugh. I am also getting an occasional #SPILL! error.

[TMS]

Try this to cope with times over midnight:

=FILTER('Sample data for help'!I:I,('Sample data for help'!C:C="E2156719")*('Sample data for help'!F:F="344-5")*('Sample data for help'!H:H="Arrived")) - FILTER('Sample data for help'!I:I,('Sample data for help'!C:C="E2156719")*('Sample data for help'!F:F="344-5")*('Sample data for help'!H:H="Dispatched"))

Note: this is the "lazy" way using full column references. It could ( WILL ) be slow if used extensively. You should use specific ranges dependent on your data.

[TMS]

I suspect the SPILL implies there are more than one entry for some of the statuses.