Deducting breaks from time measurements

[Chris Strug]

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.

 

[Roger Govier]

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