How to AVERAGE time
Table of Contents
1. How to AVERAGE time
The formula demonstrated in cell C11 calculates the average time based on the time values in cell range C3:C9. It doesn't take the date part into the calculations.
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 dates in Excel.
Step 1 - Extract whole numbers
The INT function returns the integer part of a number.
INT(number)
INT(C3:C9)
becomes
INT({42784.0758894521; 42857.9518970912; 42798.9302136859; 43012.3751426585; 42903.3770360669; 42884.6971055089; 43042.2098422595})
and returns
{42784; 42857; 42798; 43012; 42903; 42884; 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)
becomes
{42784.0758894521; 42857.9518970912; 42798.9302136859; 43012.3751426585; 42903.3770360669; 42884.6971055089; 43042.2098422595} - {42784; 42857; 42798; 43012; 42903; 42884; 43042}
and returns
{0.0758894520549802; 0.951897091246792;0.930213685918716; 0.37514265847858; 0.377036066871369; 0.69710550889431; 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.951897091246792;0.930213685918716; 0.37514265847858; 0.377036066871369; 0.69710550889431; 0.209842259464494})
and returns 0.516732388989892.
2. How to AVERAGE time hh mm ss
The formula in cell C11 calculates an average based on the differences of the Excel dates and time values in columns B and C.
Formula in cell C11:
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.
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
becomes
{42859.6205819411; 42899.94503534; 42829.9359646512; 43076.7010774007; 42919.5054573317; 42925.6235836372; 43101.0075495109}-{42784.0758894521; 42857.9518970912; 42798.9302136859; 43012.3751426585; 42903.3770360669; 42884.6971055089; 43042.2098422595}
and returns
{75.5446924890348; 41.9931382487848; 31.0057509652324; 64.3259347422427; 16.1284212648752; 40.9264781282691; 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; 41.9931382487848; 31.0057509652324; 64.3259347422427; 16.1284212648752; 40.9264781282691; 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 […]
The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]
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.