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.

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

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

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


"Studebaker" wrote:

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.

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" wrote:

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

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

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

"Studebaker" wrote in message
...
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" wrote:

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

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

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" wrote:

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

"Studebaker" wrote in message
...
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" wrote:

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

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

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

"Studebaker" wrote in message
...
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" wrote:

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

"Studebaker" wrote in message
...
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" wrote:

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

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