Calculating an average cycle time

[Bob_ipc]

Hey all, I am looking for some help on finding the average cycle time for my trucks on a load cycle. I have column "B" with the start time and date and Column "C" with the end time and date. Every now and then a driver may stop for the night in the middle of one of his loads so the cycle times that are over 8 hours would be not valid. What formula could I use to calculate the average cycle time, excluding any over 8 hours and with times that overlap midnight? I have a copy of my list of times below.

Thank you

11/26/2018 4:41:33 AM	11/26/2018 5:07:42 AM
11/26/2018 5:05:29 AM	11/26/2018 5:28:43 AM
11/26/2018 6:01:55 AM	11/26/2018 6:34:21 AM
11/26/2018 8:09:53 AM	11/26/2018 8:46:29 AM
11/26/2018 11:25:52 AM	11/26/2018 11:53:02 AM
11/26/2018 1:54:45 PM	11/26/2018 2:17:50 PM
11/26/2018 1:25:04 PM	11/26/2018 1:48:29 PM
11/26/2018 10:33:14 PM	11/26/2018 10:57:51 PM
11/26/2018 9:59:15 PM	11/26/2018 10:21:46 PM
11/26/2018 10:18:45 PM	11/26/2018 10:56:07 PM
11/26/2018 11:03:06 PM	11/26/2018 11:34:10 PM
11/23/2018 3:09:22 AM	11/23/2018 3:37:55 AM
11/23/2018 6:12:05 AM	11/23/2018 7:00:30 AM
11/23/2018 6:49:48 AM	11/23/2018 7:20:05 AM
11/27/2018 1:48:01 AM	11/27/2018 2:10:58 AM
11/27/2018 12:38:48 PM	11/27/2018 1:02:39 PM
11/27/2018 8:55:31 AM	11/27/2018 9:19:46 AM
11/27/2018 9:02:55 AM	11/27/2018 9:24:39 AM
11/27/2018 11:22:03 AM	11/27/2018 11:49:02 AM
11/27/2018 1:28:52 AM	11/27/2018 1:51:06 AM
11/27/2018 7:50:02 AM	11/27/2018 8:19:54 AM
11/27/2018 3:40:32 AM	11/27/2018 4:17:53 AM
11/27/2018 5:25:37 AM	11/27/2018 5:50:43 AM

<tbody>
</tbody>

 

[SpillerBD]

Calculate the duration with the normal = C2-B2. Format in a Time format
Also, calculate a helper column to provide an easy evaluation for whether the cycle crossed midnight.

=TRUNC(C2)-TRUNC(B2)

and format as number.

You would then apply a SUMIFS calculation like:

=AVERAGEIFS(D2:D24,D2:D24,"<"&$I$2,E2:E24,"=0")

Where values in "D" are the duration and values in "E" are a check for same day.

 

[Bob_ipc]

Hello and thank you so much for the reply!! I however am not doing something right I don't think? So I have entered =trunc(c2)-trunc(b2) and have it in the time format but I am not able to get any other result than #VALUE ! what am I doing wrong?

 

[SpillerBD]

Hello and thank you so much for the reply!! I however am not doing something right I don't think? So I have entered =trunc(c2)-trunc(b2) and have it in the time format but I am not able to get any other result than #VALUE ! what am I doing wrong?

That was me...
should B

=TRUNC(C2,0)-TRUNC(B2,0)