I am trying to calculate the time spent by operators on the shop floor
actually working on a product. I have the start times and end times but
these times also include lunch breaks. Is there anyway in Excel I can
actually subtract the time of the lunch break.

For example if the operator works from 11:30 am to 1:30 pm I would like
to subtract 30 min from it (12:00-12:30). If the operator works from
11:00 to 12:15 and then 12:30 to 2:00 pm I would like to subtract 15
min from it (12:00-12:15) and add the remaining time (12:30 -2:00). I
have 1000 rows and doing this manually would take a lot of time as well
as be prone to errors.

Can anyone recommend some easy way to do this.............

Thanks


--
badger
------------------------------------------------------------------------
badger's Profile: http://www.excelforum.com/member.php...o&userid=24642
View this thread: http://www.excelforum.com/showthread...hreadid=382274

Let assume column A is starttime B IS endtime and C is lunchstarttime
and column d is lunchendtime then number of hours worked is
=(b-A)-(D-C)

starttime endtime lunchstart lunchenD ANSWER
10:00 16:00 12:30 12:45 5:45


eg:=B2-A2-(D2-C2)


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=382274

Well this would be the case if all the lunch breaks fell within the
total time worked.

I was trying to work out a loop such that if the operation start time
was during the lunch period it would subtract the time worked in the
lunch break from the total time.Similarly if the job ended at, lets say
12:20 it would subtract 20 min from the total time as lunch starts at
12:00 pm


--
badger
------------------------------------------------------------------------
badger's Profile: http://www.excelforum.com/member.php...o&userid=24642
View this thread: http://www.excelforum.com/showthread...hreadid=382274

formula should be
A1 B1 C1 D1
E1
start end lunchstart lunch end answer
9:00 12:00 11:45 12:15 2:45

formulae is
=IF(C1B1,B1-A1,IF(AND(C1<B1,D1B1),B1-A1-(D1-B1),B1-A1-(D1-C1)))


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=382274

Wait a minute. Is someone checking to see if we're awake here?

How can someone clock out at 12:00 but yet have lunch from 11:45 to 12:15?
The formula gives the wrong answer in this case. 3 hours less 30 minutes for
lunch equals 2:30 worked, not 2:45. At least that's how it works in my
sample xls.

Am I the one who's mistaken here?
--
Summer (no valid email)


"anilsolipuram"
wrote in message
news:anilsolipuram.1r822b_1119769504.5024@excelfor um-nospam.com...
|
| formula should be
| A1 B1 C1 D1
| E1
| start end lunchstart lunch end answer
| 9:00 12:00 11:45 12:15 2:45
|
| formulae is
| =IF(C1B1,B1-A1,IF(AND(C1<B1,D1B1),B1-A1-(D1-B1),B1-A1-(D1-C1)))
|
|
| --
| anilsolipuram
| ------------------------------------------------------------------------
| anilsolipuram's Profile:
http://www.excelforum.com/member.php...o&userid=16271
| View this thread: http://www.excelforum.com/showthread...hreadid=382274
|

"Summer" wrote in
:

Wait a minute. Is someone checking to see if we're awake here?

How can someone clock out at 12:00 but yet have lunch from 11:45 to
12:15? The formula gives the wrong answer in this case. 3 hours less
30 minutes for lunch equals 2:30 worked, not 2:45. At least that's how
it works in my sample xls.

Am I the one who's mistaken here?


Maybe the formula is only meant to track work done on a specific
project, instead of work done all day. So it would be possible for the
lunch time to either be contained either fully, partially, or not at
all during the project work time.

The formula that I would use it
=IF(C1B1,B1-A1,IF(D1<A1,B1-A1,((MAX(A1:D1)-MIN(A1:D1))-(D1-C1))))

For
A1 B1 C1 D1
start end lunchstart lunchend

translated:
if lunchstart is later than project end, work time is just start to end
if lunchend is earlier than project start, work time is just start to
end
otherwise, worktime is the difference between the earliest and latest
times on the board, minus the time spent on lunch


--
Marc.