Subtracting lunch breaks from different shift times

[badger]

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

[anilsolipuram]

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)

[badger]

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

[anilsolipuram]

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


format the formulae cell as time
formulae is
=IF(C1>B1,B1-A1,IF(AND(C1<B1,D1>B1),B1-A1-(D1-B1),B1-A1-(D1-C1)))

[Summer]

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" <[email protected]>
wrote in message
news:[email protected]...
|
| 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(C1>B1,B1-A1,IF(AND(C1<B1,D1>B1),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
|

[Marc Fleury]

"Summer" <[email protected]> wrote in
news:[email protected]:

> 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(C1>B1,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.

[CindyLisa]

"Summer" <[email protected]> wrote in
news:[email protected]:

> 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(C1>B1,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.

But what if you're not tracking the break and lunch times? So you know that based on hours worked a person should get 30 minutes or 1 hour of lunch and you would like to minus the lunch whether they take lunch or not?