How do I calculation time in excel in 24 hour method

L

Lloyd

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.
 
M

Mangus Pyke

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.
Click to expand...

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-
 
M

Mangus Pyke

When time spans past midnight:

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

=B1-A1+(B1<A1)
Click to expand...

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-
 
B

Biff

At midnight a new day starts. So:

(B1<A1) = TRUE

(B1-A1)+1

It could also be written like:

=B1-A1+(A1>B1)

Biff
 
M

Mangus Pyke

At midnight a new day starts. So:

(B1<A1) = TRUE

(B1-A1)+1

It could also be written like:

=B1-A1+(A1>B1)
Click to expand...

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-
 
B

Biff

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
 
A

abcd

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
 
Top