I have a lengthy formula where I solve one problem by changing the formula
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