Calculating Time Worked in Timesheet

[Guest]

I have to maintain a timesheet and I need a function to do the math for me.
My spreadsheet looks like this basically:

A B C D
E
Time in Out for Lunch In from Lunch Time Out Total
Hours Worked

7:00am 11:00am 12:00pm 5:30pm 9.50

What formula can I put in column E that would:
a) show my 10 1/2 hours of work (from 7 to 5:30) minus my 1 hour of lunch to
equal 9 1/2 hours worked total?

b) show the Total Hours Worked in column E on a scale of 0 to 100 where 9.5
will = 9 1/2 hours worked (aka 9 hours and 30 minutes) or 9.25 will = 9 1/4
hours worked (aka 9 hours and 15 minutes)? I don't want 9:30 to display in
column E where 9:30 means 9 hours and 30 minutes worked.

Thank you for your help.

 

[T. Valko]

Will any times span past midnight?

Based on your sample:

=(lunch_out - time_in + time_out - lunch_in) * 24

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

Format as GENERAL or NUMBER

Biff

 

[Guest]

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

 

[Guest]

No, no times span past midnight.

I tried your formula and Teethless mama's and it worked on some rows but not
on others.

Ex: Time In, Out for Lunch, In from Lunch, Time Out = Total Hours Worked

1) 9:00am, 1:45, 2:45, 6:00pm = I got -4.00 with your formula but the answer
should be 8.00 hours worked.

2) 9:00, 1:15, 2:30, 5:45 = -4.50 with your formula but the answer should be
7.50 hours worked.

3) 9:00, 1:00, 2:00, 6:30 = -3.50 with your formula but the answer should be
8.50 hours worked.

Can you or somebody help?

Thanks!

 

[T. Valko]

Take a look at this screencap:

http://img19.imageshack.us/img19/7108/timevl0.jpg

If you don't explicitly include the AM/PM the time defaults to AM.

Biff

 

[Guest]

Thank you!

My timesheet was all in AM. I also didn't realize I needed my Time in, Time
out, etc. in h:mm format but my total hours worked in number format. I
corrected it and it works great!
Thank you so much for your help!

Studebaker

 

[T. Valko]

You're welcome. Thanks for the feedback!

BTW, if you enter the times in 24 hour format then you don't need to include
the AM/PM.

13:00 = 1:00 PM
18:00 = 6:00 PM
6:00 = 6:00 AM

Biff