## 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 […]

The array formula in cell D14 calculates an average based on multiple criteria in cell range B14:B15. If value in […]

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, […]

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 […]

Calculate time between time zones

The worksheet below lets you enter cities and their time difference. Excel calculatesÂ the correspondingÂ local times in E5:E8. Press F9 to […]

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