## How to AVERAGE time

*Article last updated on March 25, 2018*

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.

### Array formula

You can combine the calculations in column D by building an array formula, this makes the calculations in D3:D9 unnecessary.

The formula above is an array formula. To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Download Excel *.xlsx file

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]

How to use the AVERAGE function

Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and […]

The array formula in cell D3 calculates an average and ignores 0 (zeros). =AVERAGE(IF(B3:B8<>0,B3:B8,"")) The formula above is an array […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

Returns an integer representing the hour of an Excel time value. The returning number is ranging from 0 (12:00 A.M.) […]

How to use the MINUTE function

Returns a whole number representing the minute based on an Excel time value. The returned number is ranging from 0 […]

How to use the SECOND function

Returns an integer representing the second based on an Excel time value. The returning number is ranging from 0 to […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

Highlight specific time ranges in a weekly schedule

In a previous post I created a simple weekly schedule with dynamic dates. In this post I am going to […]

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each […]

Count entries between date and time criteria

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form