## How to AVERAGE time

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 running average of last 10 data with random blank cells

Question: List of data and blank cells in a column which will be added from day to day. There are […]

#N/A error is sometimes used to show gaps in charts, however, the AVERAGE function can't process errors, shown in C11 […]

How to calculate overlapping time 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 […]

How to calculate overlapping time ranges

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

Count complete hours between two times

The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]

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 based on date and time

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

### 2 Responses to “How to AVERAGE time”

### 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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

how to less three small time from average time

like

08:30

07:36

06:40

05:30

08:30

09:40

rahul kushwah,

can you explain in greater detail?