How to AVERAGE time
Table of Contents
1. How to AVERAGE time
The image above shows names in cells B3 to B9 and corresponding date and time values in cells C3 to C9.
The formula demonstrated in cell C11 calculates the average time of day across the dates and times listed in cells C3 through C9. However, it doesn't take the date part into the calculations which would skew the average and return an incorrect value.
This formula effectively ignores the date part and focuses only on the time of day. The result is the average time, which in this case is shown as 12:24 PM in cell C11.
This approach is useful when you want to find the average time regardless of the dates on which those times occurred.
Here's how it basically works:
- C3:C9 refers to the range of cells containing the date and time values.
- INT(C3:C9) extracts just the integer part of each date/time value, which represents the date portion.
- C3:C9 - INT(C3:C9) subtracts the date portion from the original date/time value, leaving only the time portion. In Excel, times are stored as fractional parts of a day.
- AVERAGE() then calculates the mean of these time values.
1.1 How to enter an array formula
You need to enter the formula as an array formula if you use an earlier version than Excel 365.
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.
1.2 Explaining formula
An Excel date and time value is in fact a number and a decimal part formatted as a date. For example, number 1 is 1/1/1900 and 1.5 is 1/1/1900 12:00 PM. This makes it really easy to add or subtract days to dates in Excel.
Step 1 - Extract whole numbers
The INT function returns the integer part of a number.
INT(number)
INT(C3:C9)
returns {42784; 42857; ... ; 43042}.
Notice how the values lost their decimal part, only the whole numbers remains.
Step 2 - Extract decimals
The date part is the whole number and the decimals are the times, we can extract the time part by subtracting the original numbers with the whole numbers.
The minus sign lets you subtract a number with another number in Excel formulas.
C3:C9 -INT(C3:C9)
returns {0.0758894520549802; ... ; 0.209842259464494}
Step 3 - Average decmial numbers
The AVERAGE function calculates an average.
AVERAGE(number1, [number2], ...)
AVERAGE(C3:C9 -INT(C3:C9))
becomes
AVERAGE({0.0758894520549802; ... ; 0.209842259464494})
and returns 0.516732388989892.
2. How to calculate the average of multiple time differences in this format: hours, minutes, and seconds
This example demonstrates a formula and cell formatting that allows you to calculate the mean of differences between date and time values across two columns. The cell formatting lets you show hours larger than the regular 24 hours per day meaning it shows the accumulated value in hours, minutes, and seconds.
The image shows an Excel spreadsheet with two columns of date and time data, and a calculation of the average difference between these times. Column B contains a set of dates and times, ranging from 2/18/17 1:49 AM to 11/3/17 5:02 AM. Column C contains another set of dates and times, ranging from 5/4/17 2:53 PM to 1/1/18 12:10 AM.
Date and time | Date and time |
2/18/17 1:49 AM | 5/4/17 2:53 PM |
5/2/17 10:50 PM | 6/13/17 10:40 PM |
3/4/17 10:19 PM | 4/4/17 10:27 PM |
10/4/17 9:00 AM | 12/7/17 4:49 PM |
6/17/17 9:02 AM | 7/3/17 12:07 PM |
5/29/17 4:43 PM | 7/9/17 2:57 PM |
11/3/17 5:02 AM | 1/1/18 12:10 AM |
The formula in cell C11 calculates an average based on the differences of the Excel dates and time values in columns C and B.
Formula in cell C11:
The result shown in cell C11 is 1127:02:50, which represents 1127 hours, 2 minutes, and 50 seconds. This is equivalent to about 47 days.
This calculation gives the average time span between the dates in column B and the dates in column C. It's important to note that this method assumes that all dates in column C are later than their corresponding dates in column B. If any dates in column C are earlier, it would lead to unexpected results.
The cell C11 formatting allows to display the average time in accumulated hours, minute, and seconds instead of days, hours, minute, and seconds.
Cell formatting is applied to cell C11:
- Select cell C11.
- Press CTRL + 1 to open the "Format Cells" dialog box, see the image above.
- Press with left mouse button on "Custom" category.
- Enter a new "type": [hh]:mm:ss
- Press with left mouse button on OK button.
The difference between the two cell formats in Excel is:
- 1. [hh]:mm:ss This format treats the hours as a number in square brackets, rather than a standard time format. The hours value can be greater than 24, as it represents the total number of hours, not just the hour of the day. This format is useful for displaying long durations larger than 24 hours.
- hh:mm:ss The hours value is limited to 0-23, representing the hour of the day. This format is better suited for displaying times within a 24-hour period.
If the value is 25:30:15, in the [hh]:mm:ss format it would display as 25:30:15 indicating a duration of 25 hours, 30 minutes, and 15 seconds. In the hh:mm:ss format, the same value would display as 01:30:15, which represents 1 hour, 30 minutes, and 15 seconds, however 24 hours are missing. The choice of format depends on whether you need to display durations that is longer than 24 hours or if you're working with times within a single day.
2.1 Explaining formula
An Excel date and time value is in fact a number and a decimal part formatted as a date. For example, number 1 is 1/1/1900 and 1.5 is 1/1/1900 12:00 PM. This makes it really easy to add or subtract dates in Excel.
Step 1 - Calculate difference between date and time values
The minus sign lets you subtract a number with another number in Excel formulas.
C3:C9-B3:B9
returns {75.5446924890348; ... ; 58.7977072514477}.
Step 2 - Calculate the average
The AVERAGE function calculates an average.
AVERAGE(number1, [number2], ...)
AVERAGE(C3:C9-B3:B9)
becomes
AVERAGE({75.5446924890348; ... ; 58.7977072514477})
and returns approx. 46.96 in cell C11, however, the value is formatted to show total hours, seconds and milliseconds.
Get Excel *.xlsx file
Time category
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Excel categories
3 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?
Thank you.