Excel complicated formula for a user form

Question

Some of my colleagues attend conferences, meetings and workshops in various cities. An example of a made up itinerary is shown below

The itinerary includes stopovers and each location is marked by a trip number. (A:15 to A:22) I am working on a user form which would give me the time spent in hours and minutes from the departure to the arrival time for each trip number. Note that some trips include a stopover which is why there are three trip number entries for number 1 (trip to Frankfurt via Paris) I know that the overall time spent for all these trips is 185 hours and 45 minutes as stated in L:23. In red, along raw 23 there are five formulas as follows:

C:23 shows 24/06/2016 which is =C17

D:23 shows 19:15 which is =D17

H:23 shows 16/01/2016 which is =LOOKUP(2,1/(H17:H22<>""),H17:H22) it picks up the last date inserted between H17:H22

J:23 shows 13:00 which is =LOOKUP(2,1/(J17:J22<>""),J17:J22) it picks up the last time value inserted between J17:J22

L:23 shows 185:45 hours and minutes. It is the difference between the departure date and time of the first and the arrival date and time of the last trip. (Overall time in hours and minutes) =MAX(0,(H23+J23)-(C23+D23))

I need a way to work out the total time of 185:45 broken down between various business trip numbers in C:26 to C:29. Note that trips will always be shown in a logical order i.e. 1,2,3 but the amount of legs per trip will vary depending on stop overs. The minimum amount of trips is 1 and the maximum amount of trips is 4.

Thanking you in advance Abe

Answer 1

Try this:

=TEXT(MAX(IF($A$17:$A$22=A26,$I$17:$I$22+$J$17:$J$22))-MIN(IF($A$17:$A$22=A26,$C$17:$C$22+$D$17:$D$22)),"[hh]:mm")

It is an array formula and must be confirmed with Ctrl-Shift-Enter. Put in C26, hit Ctrl-Shift-Enter then copy down.

Edit: As per OP's comments, what was wanted was the total of time from beginning of leg to the beginning of the next leg. So the formula was changed to:

=IF(MIN(IF($A$17:$A$22=A26+1,$C$17:$C$22+$D$17:$D$22))=0,MAX(IF($A$17:$A$22=A26,$I$17:$I$22+$J$17:$J$22)),MIN(IF($A$17:$A$22=A26+1,$C$17:$C$22+$D$17:$D$22)))-MIN(IF($A$17:$A$22=A26,$C$17:$C$22+$D$17:$D$22))

This is still an array formula. It needs to be confirmed by hitting Ctrl-Shift-Enter. Then copied down.