Averaging Time

[BostonBoy]

I’ve been asked to figure out how early an employee typically punches in for
work. They want to give her some sort of bonus. I have the punchclock data
in an excel column in the following format:

5/14/07 10:02 AM
5/15/07 9:16 AM
5/17/07 9:28 AM
5/18/07 9:16 AM
5/22/07 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?

 

[Barb Reinhardt]

This seems to work

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

I committed with CTRL SHIFT ENTER.

I wonder what kind of message will be sent to the workers if she gets a
bonus for punching in early. Doesn't that cost the company more money if she
works a longer day?

 

[BostonBoy]

Can this work across 2 years? It doesn't seem to give me a correct figure.

 

[BostonBoy]

Any way to make that formula work with empty rows? And across multiple years?

Well, yes it will cost a bit more, but that's the price to encourage people
to work longer hours.