Averaging Time

[BostonBoy]

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.

 

[Glenn]

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.

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.

 

[Mike H]

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]

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

 

[BostonBoy]

Got it!

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]

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

 

[David Biddulph]

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

 

[BostonBoy]

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

 

[David Biddulph]

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?

 

[BostonBoy]

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

The average of those times is 9:27. It can't be 10:02, because that's the
highest one. Perhaps you're having the same problem as me. When I add rows
in between the numbers, the ultimate average changes. Very perplexing.

If I delete the empty rows, it works. But I've got hundreds of them, and
I'm hoping there's a formula that can do it. Any other ideas?



The five values from the original example were

 

[David Biddulph]

Yes, Sorry. I must have been asleep. I fell into the old trap of
forgetting to array-enter the formula. When I enter the formula correctly I
see the problem as you describe.
To get a correct answer I used a helper column with =IF(A2<>"",MOD(A2,1),"")
and averaged that.

 

[BostonBoy]

I tried making the helper column, but if I drag the formula down the entire
helper column, then all the rows where there was no entry in A get a
"12:00am" entry, because the formula in the B helper column is acting against
a blank in the A column. So, my ultimate averaging at the bottom comes up
skewed. Any way to make a helper column without deleting every row with no
entry?

 

[Glenn]

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.

 

[BostonBoy]

Genius! Thanks, Phil

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

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

 

[David Biddulph]

If =IF(A2<>"",MOD(A2,1),"") is returning a zero, then column A isn't blank.