Column C contains both date and time, to calculate the average of only time we need to extract the hours, minutes and seconds from column C. Formula in cell D3:
Copy cell D3 and paste to cells below. The HOUR function gets the hour from column C, the MINUTE function gets the minute from column C and so on.
The TIME function then builds an Excel timevalue allowing you to calculate the average in cell D11:
Why go through all this mess? The date part in column C is actually a whole number between 1 and 99999. 1 is 1/1/1900 and 1/1/2017 is 42736. If we use the dates in our average calculation the result will come out wrong.
The time part is a number between 0 and 1. For example 01:00 AM is 1/24 or 0.04167, 12:00 PM is 12/24 or 0.5.
You can combine the calculations in column D by building an array formula, this makes the calculations in D3:D9 unnecessary.