Deducting breaks from time measurements

Hi,

First part pretty simple. Column A = Start Time, Column B = End Time. As
times always occur in the same day then to find the time spent is a simple
"B-A".

However for the next part...

I wish to be able to deduct break times from the time spent.

If we have three breaks a day between say
10:00 - 10:30
12:30 - 13:30
14:30 - 15:00

then if any of these intervals occur between the start time and end time
then i would like to deduct the period of the break (i.e. 30 mins or 1
hour).

I could probably achieve this using lots of nested IFs and lookups but I was
wondering if there was an easier way?

Any and all advice is gratefully received.

regards

Chris.

Hi Chris

The following is definitely not pretty, and I am sure there is a simpler
way, but this returns the correct answers in decimal hours
=(B12-A12-(--AND(A12<TIME(10,0,0),B12>TIME(10,30,0))*30/1440)-(--AND(A12<TIME(12,30,0),B12>TIME(13,30,0))*60/1440)-(--AND(A12<TIME(14,30,0),B12>TIME(15,0,0))*30/1440))*24

Regards

Roger Govier



Chris Strug wrote:

>Hi,
>
>First part pretty simple. Column A = Start Time, Column B = End Time. As
>times always occur in the same day then to find the time spent is a simple
>"B-A".
>
>However for the next part...
>
>I wish to be able to deduct break times from the time spent.
>
>If we have three breaks a day between say
>10:00 - 10:30
>12:30 - 13:30
>14:30 - 15:00
>
>then if any of these intervals occur between the start time and end time
>then i would like to deduct the period of the break (i.e. 30 mins or 1
>hour).
>
>I could probably achieve this using lots of nested IFs and lookups but I was
>wondering if there was an easier way?
>
>Any and all advice is gratefully received.
>
>regards
>
>Chris.
>
>
>
>