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
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
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, […]
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.
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?