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),B12TIME(10,30,0))*30/1440)-(--AND(A12<TIME(12,30,0),B12TIME(13,30,0))*60/1440)-(--AND(A12<TIME(14,30,0),B12TIME(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.
|