Calculate hours worked

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
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)
 
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.
 
Back
Top