How do I calculation time in excel in 24 hour method
 

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

Consider this:

Excel stores time as a fractional part of a day. So the value of a day is 1.
One hour equals 1/24.

11:00 PM is really the formatted decimal value 23/24 = 0.9583333333333

Here's the same formula done in GENERAL format:

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

0.958333333333333 - 0.291666666666667 = -0.666666666666667 + 1 =
0.333333333333333

0.333 is one third of a day and with the cell format returns 8:00

If you wanted the result in decimal format you would multiply by 24. SO:

=(B1-A1+(B1<A1))*24 = 8

The result of B1 - A1 is a negative time. There's no such thing as a
negative time so in essence, because a new day starts at midnight, we add 1
day to the result of the subtraction operation.

Biff

"Mangus Pyke" wrote in message
...
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

the other thing you need to know is that excel auto-convert data
formats. So adding a boolean(true/false) with a number as no sense.

But excel suppose that if you add it, that means you wish to convert it
to a number: so it makes true=1 and false=0