Time Sheet Calculation

F

Frantic Excel-er

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!!!
 
D

Dave F

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)
 
F

Frantic Excel-er

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

Dave F said:
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)

Frantic Excel-er said:
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!!!
Click to expand...
Click to expand...
 
C

Chris

Hello,

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

Dave F

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

Frantic Excel-er said:
I cannot get this formula to work. It does not recognize the "N" for the
column that I have. ???

Dave F said:
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)

Frantic Excel-er said:
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!!!
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

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

and format as time

--
HTH

Bob Phillips

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

Dave F

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

More simple than mine.
 
B

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