S

#### Scott

and another keeps seeming to occur. In the last column shown in the data

below I am trying to calculate the amount of time that has passed between the

stop time of one row and the start time of the next row but there are a lot

of IF's. For example: if the start time is before 9:00 a.m. then the space

should be blank; if the start time is between 9:00 a.m. and 10:00 a.m. then

it should be subtracted by 9:00 instead of the previous Stop time; if the

hour is <4 then use the formula ROUND(MOD(A3-B2),1)*1440,0) instead; if it is

the first entry or there is no entry then the space in column D should be

blank. The current formula I have in column D is as follows:

=IF(OR(A1<>"Start",A2<>""),(IF(OR(HOUR(A2)>=9,(AND(HOUR(A2)<4,MIN(A2)>0))),ROUND(MOD(A2-MAX(R2,"9:00"),1)*1440,0),0)),0)

This formula works until I have two entries that occur after midnight as you

can see below. I haven't been able to insert the ROUND(MOD(A3-B2),1)*1440,0)

formula for HOUR < 4 without getting an error.

Any help would be greatly appreciated. Plus if there is a way to shorten

parts of the formula that I have so far that would be great. I think I am

making this more complicated than it should be.

Thanks,

Scott

A B C D

Start Stop Time

7:20 7:36 0:16

8:34 8:45 0:11

9:45 9:52 0:07 45

10:20 10:31 0:11 28

11:02 11:12 0:10 31

12:15 12:27 0:12 63

13:07 13:54 0:47 40

14:20 15:04 0:44 26

17:47 18:00 0:13 163

18:44 18:51 0:07 44

19:15 19:37 0:22 24

20:34 20:42 0:08 57

22:04 22:43 0:39 82

23:33 23:38 0:05 50

1:00 1:10 0:10 82