Help with calculating time difference using two criteria fields

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

Originally Posted by TMS

Is this what you want:

Formula:

Please Login or Register  to view this content.

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

Originally Posted by TMS

Maybe this:

Formula:

Please Login or Register  to view this content.

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