Averaging times that span midnight

rspizman

New Member
Joined
Feb 3, 2005
Messages
3
I have a question about averaging specific time events when times span midnight.

I am required to track the times computer files are uploaded to clients. I was asked to provide weekly averages by shift. For our second shift, approximately 20% of the upload times occur before midnight. During the course of a week, there is an average of 500 data points. The data is electronically mined out of our computer system. Below is a small sampling:

1/7/2005 23:16
1/7/2005 23:54
1/7/2005 23:54
1/8/2005 0:18
1/8/2005 0:24
1/8/2005 3:00
1/8/2005 3:00
1/10/2005 2:10
1/10/2005 3:11
1/10/2005 3:11
1/10/2005 3:11

What is wanted is the average TIME, the date is completely arbitrary. The average of the above data is 01/08/05 19:02. If I average just the time, I get 08:08. If I manually alter the dates so all the times between 00:00 and and 04:00 (in this case) are today’s date and all uploads greater than 20:00 hours are yesterday’s date, I get the correct average of 01:35.

Is there any way to calculate the average time without manually altering the dates, especially considering I am dealing with 500 data points per week?

Thanks,
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Welcome to the board!

I get an average of 1:40AM for 1/8/2005, using the formula in D2 below: =AVERAGE(IF(C2:C12=DATEVALUE("1/8/2005"),B2:B12)), which is confirmed with CTRL+SHIFT+ENTER

B2 =TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
C2 =DATE(YEAR(A2),MONTH(A2),DAY(A2))
Book5
ABCD
1OriginalTimeDate
21/7/200523:1611:16PM1/7/20051:40AM
31/7/200523:5411:54PM1/7/2005
41/7/200523:5411:54PM1/7/2005
51/8/20050:1812:18AM1/8/2005
61/8/20050:2412:24AM1/8/2005
71/8/20053:003:00AM1/8/2005
81/8/20053:003:00AM1/8/2005
91/10/20052:102:10AM1/10/2005
101/10/20053:113:11AM1/10/2005
111/10/20053:113:11AM1/10/2005
121/10/20053:113:11AM1/10/2005
Sheet1
 

Eric B

Board Regular
Joined
Jun 16, 2002
Messages
108
If you want to calculate the average from a range of dates, you could also try
pasting the following in B2 and copying down

=IF(HOUR(A2)+MINUTE(A2)/60<4,HOUR(A2)+MINUTE(A2)/60+24,HOUR(A2)+MINUTE(A2)/60)
this turns time into regular numbers (hours + minutes as fractions of hours) and adds 24 hours to the hour value of times between midnight and 4am (the end of this shift?- you can adjust that if you need to)

Then, you can take the simple average of the B column and convert it back to a time by
=TIME(AVERAGE(B2:B12),MINUTE(MOD(AVERAGE(B2:B12),1)*60),0)
Using B2:B12 as an example

HTH
 

Dutchy

Well-known Member
Joined
Sep 18, 2004
Messages
560
....or just use the following formula with CTRL+SHIFT+ENTER

=AVERAGE(A1:A11-ROUND(A1:A11,0))+1

where your data is in A1:A11

and format as a time e.g. "hh:ss"

This will give you an average time after midnight, irrespective of the date - the "+1" at the end simply ensures that the time is still displayed if the result is negative (i.e. before midnight) but you may wish to dispense with this if the result is to be used for further calculations.
Book1
ABCD
101/07/200523:16
201/07/200523:54
301/07/200523:54
401/08/200500:18
501/08/200500:24
601/08/200503:00
701/08/200503:00
801/10/200502:10
901/10/200503:11
1001/10/200503:11
1101/10/200503:11
12
131:35:22
Sheet2
 

Dutchy

Well-known Member
Joined
Sep 18, 2004
Messages
560
..or probably slightly better.

=MOD(AVERAGE(A1:A11-ROUND(A1:A11,0))+1,1)

again confirmed with CTRL+SHIFT+ENTER
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top