Time Sheet Calculation

[Guest]

Hello Everyone -

I have four columns a,b,c,d...column A = time in, column B = time out for
lunch, column C = time back in from lunch, and column D = time out for the
day.

A B C D E
8:00 12:00 12:30 5:00 =?

How do I calculate the total time spent at work? I am sure this is very
simple, but I can't figure it out. The result needs to be displayed in a
number format - like for this item, this would be 8.5 hours. Also, one other
wrench might be that someone doesn't take a lunch for the day because they
may only work for 5 hours, so they would put time in (column a) and time out
(column D) with no data in between.

Any help is greatly appreciated!!!!!

thanks!!!

 

[Guest]

If someone comes in at 12PM and leaves at 8PM, then (8PM - 12PM) * 24 = 8.

In excel terms: assume A1 = 12 PM, B1 = 8PM. Then: (B1-A1)*24 = 8. Make
sure the cells are formatted for time, not integers, for this calculation.

As to the issue of taking time for lunch: include a helper column, "lunch
taken?" which you populate with Y or N as appropriate. Assume column E is
the helper column.

Then: IF(E="N",(B1-A1)*24,(b1-A1-[TIMETAKEN])*24)

 

[Guest]

I cannot get this formula to work. It does not recognize the "N" for the
column that I have. ???

If someone comes in at 12PM and leaves at 8PM, then (8PM - 12PM) * 24 = 8.

In excel terms: assume A1 = 12 PM, B1 = 8PM. Then: (B1-A1)*24 = 8. Make
sure the cells are formatted for time, not integers, for this calculation.

As to the issue of taking time for lunch: include a helper column, "lunch
taken?" which you populate with Y or N as appropriate. Assume column E is
the helper column.

Then: IF(E="N",(B1-A1)*24,(b1-A1-[TIMETAKEN])*24)

Hello Everyone -

I have four columns a,b,c,d...column A = time in, column B = time out for
lunch, column C = time back in from lunch, and column D = time out for the
day.

A B C D E
8:00 12:00 12:30 5:00 =?

How do I calculate the total time spent at work? I am sure this is very
simple, but I can't figure it out. The result needs to be displayed in a
number format - like for this item, this would be 8.5 hours. Also, one other
wrench might be that someone doesn't take a lunch for the day because they
may only work for 5 hours, so they would put time in (column a) and time out
(column D) with no data in between.

Any help is greatly appreciated!!!!!

thanks!!!

 

[Chris]

Hello,

Try formatting your cells as Time and then in Column E, put this
forumula "=(D4-A4)-(C4-B4)"

 

[Guest]

Hi--

The column that has the N in it needs to have a proper cell reference. If E
is the column with the N or Y:

IF(E1="N",(B1-A1)*24,(b1-A1-[TIMETAKEN])*24)

(Note in the original formula I gave you I didn't have a proper cell
reference for columnE, which is probably why it's not working for you.)

I cannot get this formula to work. It does not recognize the "N" for the
column that I have. ???

If someone comes in at 12PM and leaves at 8PM, then (8PM - 12PM) * 24 = 8.

In excel terms: assume A1 = 12 PM, B1 = 8PM. Then: (B1-A1)*24 = 8. Make
sure the cells are formatted for time, not integers, for this calculation.

As to the issue of taking time for lunch: include a helper column, "lunch
taken?" which you populate with Y or N as appropriate. Assume column E is
the helper column.

Then: IF(E="N",(B1-A1)*24,(b1-A1-[TIMETAKEN])*24)

Hello Everyone -

I have four columns a,b,c,d...column A = time in, column B = time out for
lunch, column C = time back in from lunch, and column D = time out for the
day.

A B C D E
8:00 12:00 12:30 5:00 =?

How do I calculate the total time spent at work? I am sure this is very
simple, but I can't figure it out. The result needs to be displayed in a
number format - like for this item, this would be 8.5 hours. Also, one other
wrench might be that someone doesn't take a lunch for the day because they
may only work for 5 hours, so they would put time in (column a) and time out
(column D) with no data in between.

Any help is greatly appreciated!!!!!

thanks!!!

 

[Bob Phillips]

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

and format as time

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Yeah I think Bob's solution makes sense if you assume everyone takes lunch.

More simple than mine.

 

[Bob Phillips]

Actually, Chris' solution is along the same lines as mine, but will work
even if they don't take lunch, therefore even better..

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)