Calculate hours worked

[Guest]

Greetings!

We are using the 12 hour clock format in our time sheet and were able to
calculate the hours worked, however, we want to display the time 4:00 PM as
4:00 without the PM with the same calculation result.
A B C D E
IN OUT IN OUT HOURS
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.0

We use the formula in column E =(D-C-INT(D-C))*24+(B-A-INT(B-A))*24 and
format it as Number with 1 decimal places.

Using the format hh:mm in column A & B we can display the time with out the
AM/PM as it less than 1:00PM, however beyond 1:00PM
using the same format 1:00PM will become 13:00 which is not the desired time
display(in print & on screen). Typing just 4:00 will neither give a correct
answer. Pls advice. We use Excel 2003 version.

Thanks and regards

 

[Bob Phillips]

Don't multiply by 24, and format as hh:mm perhaps

=(D2-C2-INT(D2-C2))+(B2-A2-INT(B2-A2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Mr. Bob,

Thanks for the reply. We can now type the time and display as it is without
the AM/PM. However, using the formula we still have this problem as shown
below:
A B C D E
IN OUT IN OUT HRS WORK
Larry 7:00 12:00 1:00 4:00 8.0
Jimmy 7:30 12:00 1:00 4:00 7:30

For Jimmy the number of hours work should be 7.5 and not 7:30. How to
change/format 7:30 to 7.5 using the same formula. We compute his salary by
the number of hours work.
Thanks and best regards.

Larry

 

[Bob Phillips]

Then use

=((D2-C2-INT(D2-C2))+(B2-A2-INT(B2-A2)))*24

and format as General


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Mr. Bob,

Solved. It's a great help for us.

Thank you very much.

Larry

 

[Guest]

I type exact formula as you did, but why em I not getting the answer to
colume E, the formula stays rather it should change to 8 hours. Can you
please send me the formula for this IN 6:00AM LUNCH 12:00 IN 12:30 OUT 5:30.