I'm trying to set up a time sheet for police volunteers to log in hours they
work. I would like to have the times inputed in the 24 hour method (but the
12 hour method would also work) and then have it calculate each days time. I
also need end of week and month totals.

On Sun, 17 Jul 2005 21:10:03 -0700, "Lloyd"
wrote:

I'm trying to set up a time sheet for police volunteers to log in hours they
work. I would like to have the times inputed in the 24 hour method (but the
12 hour method would also work) and then have it calculate each days time. I
also need end of week and month totals.

ColA ColB ColC
Start Stop Calculation
8:00 20:00 B2-A2 (which will yield 12:00 hours)

Where you'll run into a problem is if you have a night shift, such as:

22:00 6:00 Error

You'd have to use:

22:00 30:00 8:00

Unless someone else has a better way of doing it.

I left police work to be a database analyst.. haven't gotten shot
since, and haven't had to work night shifts.

:)

MP-

--
"Learning is a behavior that results from consequences."
B.F. Skinner

Hi!

When time spans past midnight:

A1 = 11:00 PM
B1 = 7:00 AM

=B1-A1+(B1<A1)

Biff

"Mangus Pyke" wrote in message
...
On Sun, 17 Jul 2005 21:10:03 -0700, "Lloyd"
wrote:

I'm trying to set up a time sheet for police volunteers to log in hours
they
work. I would like to have the times inputed in the 24 hour method (but
the
12 hour method would also work) and then have it calculate each days time.
I
also need end of week and month totals.

ColA ColB ColC
Start Stop Calculation
8:00 20:00 B2-A2 (which will yield 12:00 hours)

Where you'll run into a problem is if you have a night shift, such as:

22:00 6:00 Error

You'd have to use:

22:00 30:00 8:00

Unless someone else has a better way of doing it.

I left police work to be a database analyst.. haven't gotten shot
since, and haven't had to work night shifts.

:)

MP-

--
"Learning is a behavior that results from consequences."
B.F. Skinner

On Mon, 18 Jul 2005 00:43:30 -0400, "Biff"
wrote:
When time spans past midnight:

A1 = 11:00 PM
B1 = 7:00 AM

=B1-A1+(B1<A1)

Works like a charm. I have no idea why.. but it does.

What exactly is that formula doing, and specifically the part in
parentheses?

MP-

--
"Learning is a behavior that results from consequences."
B.F. Skinner

At midnight a new day starts. So:

(B1<A1) = TRUE

(B1-A1)+1

It could also be written like:

=B1-A1+(A1B1)

Biff

"Mangus Pyke" wrote in message
...
On Mon, 18 Jul 2005 00:43:30 -0400, "Biff"
wrote:
When time spans past midnight:

A1 = 11:00 PM
B1 = 7:00 AM

=B1-A1+(B1<A1)

Works like a charm. I have no idea why.. but it does.

What exactly is that formula doing, and specifically the part in
parentheses?

MP-

--
"Learning is a behavior that results from consequences."
B.F. Skinner

On Mon, 18 Jul 2005 01:10:42 -0400, "Biff"
wrote:
At midnight a new day starts. So:

(B1<A1) = TRUE

(B1-A1)+1

It could also be written like:

=B1-A1+(A1B1)

There are few things about Excel that stump me.

I think you've just found one.

That makes absolutely no sense to me.

I understand the test for whether B1 is less than A1, and I understand
why that would return TRUE.

I don't understand how that forces B1-A1 to return the correct time by
adding "TRUE" to it.

MP-

--
"Learning is a behavior that results from consequences."
B.F. Skinner