How do I get Google Sheets to give me the average time of day in a series of dates?

Question

I need to find the average time of day over the course of several days.

            Column A
Row 1 - 07/13/14 02:45 PM
Row 2 - 07/12/14 10:45 PM
Row 3 - 07/12/14 04:07 PM
Row 4 - 07/11/14 12:30 AM
Row 5 - 07/11/14 06:15 PM
Row 6 - 07/10/14 05:30 PM
Row 7 - 07/10/14 01:00 AM
Row 8 - 07/10/14 04:00 AM

=AVERAGE(A1:A8) returns 7/11/14 2:51 PM. The average of these times should be 8:51 PM. AVERAGE seems to be incorporating the date in the average and I can't have that.

=AVERAGE(TIMEVALUE((A1:A8)) returns #VALUE.

The only way I can get 8:51 PM is by first converting each cell in Column A to either 1/1/14 or 1/2/14 so the dates remain in the 24 hour range by using

=IF(AND(TIMEVALUE(A1) > TIMEVALUE("12:00 AM"), TIMEVALUE(A1) < TIMEVALUE("5:00 AM")),DATEVALUE("1/2/14") + TIMEVALUE(A1), DATEVALUE("1/1/14") + TIMEVALUE(A1))

and then taking the average of the converted cells, which are in Column B

            Column B
Row 1 - 01/01/14 02:45 PM
Row 2 - 01/01/14 10:45 PM
Row 3 - 01/01/14 04:07 PM
Row 4 - 01/02/14 12:30 AM
Row 5 - 01/01/14 06:15 PM
Row 6 - 01/01/14 05:30 PM
Row 7 - 01/02/14 01:00 AM
Row 8 - 01/02/14 04:00 AM

I tried using

=IF(AND(TIMEVALUE(A1:A8) > TIMEVALUE("12:00 AM"), TIMEVALUE(A1:A8) < TIMEVALUE("5:00 AM")),DATEVALUE("1/2/14")+DATEVALUE(A1:A8), datevalue("1/1/14")+TIMEVALUE(A1:A8))

but that also returns #VALUE.

Thanks in advance.

Answer 1

From what I can understand, the "correct average" you are providing is in fact wrong. You said

The average of these times should be 8:51 PM.

While I am finding it to be 11:51:30 AM.

In any case, when extracting timevalues from dates, this formula should do the trick:

=ARRAYFORMULA(AVERAGE(TIMEVALUE(A1:A8)))

You may want to take a look at ARRAYFORMULA API, but here's a description extract:

[ARRAYFORMULA] Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.