Can't get accurate average time

[PHIL.Pearce84]

Hi,

I have a list of times for technicians over the course of the month showing the time they arrived home, I am trying the identify what the average arrival time for each person is for the month. However whenever I use AVERAGE() I get some rather strange results? Can anyone help? I have pasted some sample data below.

[TABLE="width: 342"]
<tbody>[TR]
[TD]Average Arrive Home Time[/TD]
[TD]Bob[/TD]
[TD]Fred[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2017[/TD]
[TD="align: right"]18:30:27 [/TD]
[TD="align: right"]17:25:03 [/TD]
[/TR]
[TR]
[TD="align: right"]02/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2017[/TD]
[TD="align: right"]17:44:14 [/TD]
[TD="align: right"]17:31:13 [/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2017[/TD]
[TD="align: right"]17:55:50 [/TD]
[TD="align: right"]17:30:09 [/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2017[/TD]
[TD="align: right"]17:45:28 [/TD]
[TD="align: right"]17:23:19 [/TD]
[/TR]
[TR]
[TD="align: right"]07/09/2017[/TD]
[TD="align: right"]18:03:59 [/TD]
[TD="align: right"]17:37:58 [/TD]
[/TR]
[TR]
[TD="align: right"]08/09/2017[/TD]
[TD="align: right"]18:45:14 [/TD]
[TD="align: right"]16:23:42 [/TD]
[/TR]
[TR]
[TD="align: right"]09/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]10/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]11/09/2017[/TD]
[TD="align: right"]18:53:20 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]12/09/2017[/TD]
[TD="align: right"]17:47:19 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]13/09/2017[/TD]
[TD="align: right"]18:52:58 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]14/09/2017[/TD]
[TD="align: right"]17:48:03 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2017[/TD]
[TD="align: right"]17:40:26 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]16/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]17/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]18/09/2017[/TD]
[TD="align: right"]17:35:04 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2017[/TD]
[TD="align: right"]17:26:48 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]20/09/2017[/TD]
[TD="align: right"]17:53:44 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]21/09/2017[/TD]
[TD="align: right"]17:39:36 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]22/09/2017[/TD]
[TD="align: right"]18:07:23 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]23/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]24/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
[TR]
[TD="align: right"]25/09/2017[/TD]
[TD="align: right"]18:38:01 [/TD]
[TD="align: right"]17:46:50 [/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2017[/TD]
[TD="align: right"]17:04:09 [/TD]
[TD="align: right"]17:26:41 [/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2017[/TD]
[TD="align: right"]17:59:23 [/TD]
[TD="align: right"]15:49:45 [/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2017[/TD]
[TD="align: right"]18:17:31 [/TD]
[TD="align: right"]17:25:54 [/TD]
[/TR]
[TR]
[TD="align: right"]29/09/2017[/TD]
[TD="align: right"]18:06:26 [/TD]
[TD="align: right"]17:58:59 [/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2017[/TD]
[TD="align: right"]00:00:00 [/TD]
[TD="align: right"]00:00:00 [/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[joeu2004]

whenever I use AVERAGE() I get some rather strange results?

With a 127 posts here, I would think you would know to show us your formula, the result and what you consider "strange" about it.

If Bob's arrival times are in B2:B31, the average arrival time would be:

=AVERAGE(B2:B31)
or
=AVERAGEIF(B2:B31,"<>0")
or
=AVERAGE(IF(B2:B31<>0,B2:B31))

The latter two exclude "arrival times" of zero. The last formula must be array-entered (press ctrl+shift+Enter instead of just Enter); use it if you save as "xls".

You might need to format the cell as Custom hh:mm:ss to avoid "strange" results like 0.7512.

 

[PHIL.Pearce84]

Thank you for your comments, I have applied the formula but I am getting a result of 12:18:50 but given that all times are after 17:30 and before 19:00 I would expect something in the middle?

 

[applebyd]

How about multiplying by 86400 to take it into seconds since midnight.

You are then averaging whole numbers.

Then just convert back to a time, should save the 'oddness' going on.

Regards

DaveA