Subtracting lunch breaks from different shift times

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" <anilsolipuram.1r822b_1119769504.5024@excelforum-nospam.com>
wrote in message
news:anilsolipuram.1r822b_1119769504.5024@excelforum-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(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
|

"Summer" <summer@thecabinbythelake.com> wrote in
news:_vFve.1209$Q75.165416@newshog.newsread.com:

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

Originally Posted by Marc Fleury

"Summer" <summer@thecabinbythelake.com> wrote in
news:_vFve.1209$Q75.165416@newshog.newsread.com:

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