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

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

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

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

"Dave F" wrote:

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

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

Hello,

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


Frantic Excel-er wrote:
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!!!

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

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

"Dave F" wrote:

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

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

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

and format as time

--
HTH

Bob Phillips

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

"Frantic Excel-er" wrote in
message ...
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!!!

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

More simple than mine.


"Bob Phillips" wrote:

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

and format as time

--
HTH

Bob Phillips

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

"Frantic Excel-er" wrote in
message ...
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!!!

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)

"Dave F" wrote in message
...
Yeah I think Bob's solution makes sense if you assume everyone takes
lunch.

More simple than mine.


"Bob Phillips" wrote:

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

and format as time

--
HTH

Bob Phillips

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

"Frantic Excel-er" wrote in
message ...
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!!!