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?

 

[Bob Bridges]

Consider how a timestamp is stored in Excel: It's a floating-point number
with the integer part indicating the number of days since, um, I think it's
since 1900-01-01, and the fractional part indicating the portion of the day,
starting at midnight. That is, the current time on my machine as I write
this is 39682.81327, which is displayed as 2008-08-22 19:31; back at exactly
noon the timestamp would've said 39682.5 and at 8:00 pm (two thirds of the
way between noon and midnight, five sixths of the day gone) it'll be
39682.83333.

What you want, then, is to average the fractional parts of each date. Can
you take it from there?