title: How do I get Google Sheets to give me the average time of day in a series of dates?
body: <p>I need to find the average time of day over the course of several days.</p>
  
  <pre><code>            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
  </code></pre>
  
  <p><code>=AVERAGE(A1:A8)</code> returns <code>7/11/14 2:51 PM</code>.  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.  </p>
  
  <p><code>=AVERAGE(TIMEVALUE((A1:A8))</code> returns <code>#VALUE</code>.</p>
  
  <p>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</p>
  
  <p><code>=IF(AND(TIMEVALUE(A1) &gt; TIMEVALUE("12:00 AM"), TIMEVALUE(A1) &lt; TIMEVALUE("5:00 AM")),DATEVALUE("1/2/14") + TIMEVALUE(A1), DATEVALUE("1/1/14") + TIMEVALUE(A1))</code></p>
  
  <p>and then taking the average of the converted cells, which are in Column B</p>
  
  <pre><code>            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
  </code></pre>
  
  <p>I tried using</p>
  
  <p><code>=IF(AND(TIMEVALUE(A1:A8) &gt; TIMEVALUE("12:00 AM"), TIMEVALUE(A1:A8) &lt; TIMEVALUE("5:00 AM")),DATEVALUE("1/2/14")+DATEVALUE(A1:A8), datevalue("1/1/14")+TIMEVALUE(A1:A8))</code></p>
  
  <p>but that also returns <code>#VALUE</code>.</p>
  
  <p>Thanks in advance.</p>