Need help with averaging a time but dates are messing up the result I am trying for.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chadsxe - 15 Jun 2006 16:22 GMT

I am attempting to get a worksheet that averages times but I am running
into a few problems. For example I have this setup....

A1 = 05/11/06 11:00 PM
B1 = 05/13/06 11:30 PM
C1 = 05/15/06 12:00 AM
D1 = 05/17/06 12:30 AM
E1 = 05/19/06 01:00 AM
F1 = =AVERAGE(A1:E1)

In F1 I am getting 9:36 AM which I completely understand (it is
averageing from 5/11 11PM to 5/19 1AM) but this is not what I am
looking for. I want it to just average the time and have the result of
F1 show up as 12:00AM. I tried removing the dates but then it was
averaging only in a one day cycle. So it seems the dates are the only
way I can figure out how to tell it to average from lets say 11:00 at
night to 12:30 the next morning. But as you can see I don't want it to
average on the date just the time.

Thanks

Chad

Signature

chadsxe

Reply to this Message

Bernie Deitrick - 15 Jun 2006 16:54 GMT

Chad,

Array enter (enter using Ctrl-Shift-Enter)

=AVERAGE(A1:E1-INT(A1:E1))

Note that the average isn't around midnight, since times are from 0 - Midnight to .5 Noon to 1 the
next Midnight

HTH,
Bernie
MS Excel MVP

> I am attempting to get a worksheet that averages times but I am running
> into a few problems. For example I have this setup....
[quoted text clipped - 18 lines]
>
> Chad

Reply to this Message

chadsxe - 15 Jun 2006 17:12 GMT

Bernie Deitrick Wrote:

> Chad,
>
[quoted text clipped - 9 lines]
> Bernie
> MS Excel MVP

I don't mean to sound stupid and bug you but that went way over my
head. Can you please try and explain it in a diffrent way.

Thanks

Chad

Signature

chadsxe

Reply to this Message

Bernie Deitrick - 15 Jun 2006 17:27 GMT

Chad,

Excel stores dates as integers, with December 31, 1899 as 0, incrementing 1 for every day since
then. So June 15, 2006 is actually stored as the number
38883. This allows dates to be subtracted, etc. Anyway, Excel stores times as fractions of a day:
take the time in military format, and divide by 24. So 10 AM is 10/24, or .416666666. Date/time
values are stored as decimal nubers, so 10 AM on June 15, 2006 is 38883.4166666666 Then Excel
formats these to show the date or time however you want.

So, if the value June 16, 2006 10:00 AM is in cell A1, using

=INT(A1) will return 38883 (just the date), and using

=A1-INT(A1) will return 0.4166666666 (just the time)

So my formula returns the average of just the times.... Format the cell with my formula as time, and
it will give you the average time as a time value....

HTH,
Bernie
MS Excel MVP

> Bernie Deitrick Wrote:
>> Chad,
[quoted text clipped - 17 lines]
>
> Chad

Reply to this Message

chadsxe - 15 Jun 2006 17:48 GMT

O.k. that makes sense and I have tested it out but there is still that
one flaw. Say for example

A1 = 11:30PM (driver come in at 11:30 at night)
A2 = 12:00AM (driver comes in a half hour later at 12:00 in the
morning)

The formula you suggested looks at this and says this is on the same
day. But in reality it is not no the same dam and the average between
the 2 should be 11:45PM. The formula you suggested works fine if the
drivers come in on the same day. But as soon as some one comes in past
the Mid-night mark it then throughs that time back into the same 24 hour
block as the rest of them. Did that make sense?

Signature

chadsxe

Reply to this Message