Averaging Time
 

Ive 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

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?

Averaging Time
 

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?
--
HTH,
Barb Reinhardt



"BostonBoy" wrote:

Ive 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

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?

Averaging Time
 

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.

Here's one way.

Start with the data in column A.

In B1, put
=A1-INT(A1)
and copy down as far as needed. This is the time-of-day for each day's
arrival.

For the average arrival time, use
=AVERAGE(B:B)
and format as time.

Averaging Time
 

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

"Barb Reinhardt" wrote:

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?
--
HTH,
Barb Reinhardt



"BostonBoy" wrote:

Ive 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

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?

Averaging Time
 

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.

"Barb Reinhardt" wrote:

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?
--
HTH,
Barb Reinhardt



"BostonBoy" wrote:

Ive 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

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?