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

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

The AVERAGE function is designed to ignore blank cells but there are instances where it fails. The picture above seems […]

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

The DATEDIF function in cell E3 allows you to calculate days between two dates. Related articles Use MEDIAN function to […]

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

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