Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.

BostonBoy wrote:
Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.


With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

First of all, I'm sorry for the multiple posts. My browser kept showing
error messages upon hitting the send button.

I tried the method you suggested and received the #value! error message.

The original cells are formatted as date cells. I've changed their format
to time cells, but that isn't working either. Any other ideas on this one?
I've got daily entries for 2 years, so copying them into a new space is time
consuming


"Glenn" wrote:

BostonBoy wrote:
Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.


With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

Got it!

"Glenn" wrote:

BostonBoy wrote:
Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.


With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.

"Glenn" wrote:

BostonBoy wrote:
Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.


With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically punches in
for
work, as we want to give her a bonus. I have the punch clock data in
an
excel column in the following format (a few of the entries from this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes.
I've
been having trouble with the log-on for the last hour.

Didn't work for me. The formula seems affested by the empty cells in between
the numbers. Any ideas?

"David Biddulph" wrote:

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically punches in
for
work, as we want to give her a bonus. I have the punch clock data in
an
excel column in the following format (a few of the entries from this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes.
I've
been having trouble with the log-on for the last hour.

I've got the 5 values from your original example. I average them with the
formula which Glenn suggested, & get 10:02. If I include blank lines so
that the formula now calls up a range of 9 cells, 4 of them blank, the
average is stll 10:02.

What values do you have, what formula, what result did you get, and what
result did you expect?
--
David Biddulph

"BostonBoy" wrote in message
...
Didn't work for me. The formula seems affested by the empty cells in
between
the numbers. Any ideas?

"David Biddulph" wrote:

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the
entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically punches
in
for
work, as we want to give her a bonus. I have the punch clock data
in
an
excel column in the following format (a few of the entries from this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to
figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of
minutes.
I've
been having trouble with the log-on for the last hour.

BostonBoy wrote:
Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.


=AVERAGE(IF(A1:A999="","",MOD(A1:A999,1)))

Again, array-entered. Adjust the range as needed.

Genius! Thanks, Phil

"Glenn" wrote:

BostonBoy wrote:
Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.


=AVERAGE(IF(A1:A999="","",MOD(A1:A999,1)))

Again, array-entered. Adjust the range as needed.

Hi,

Extract the time into a helper column with

=TIME(HOUR(A1),MINUTE(A1),0)

Drag down as required and average these and you should get 09:27

Mike

"BostonBoy" wrote:

Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.