For this example, we will use the top line of data so...

Type = H6 
Time Depart = I6
Time landed = J6
Single Engine In Command = K6
Single Engine Dual = L6

My plan is to add I6 and J6 into L6 if H6 says Dual. But I6 to add with J6 into K6 if H6 says In Command.

Can anyone help me with my boxes? In L6 I already have the formula (to make box blank if no values in I6 & J6;

=IF(AND(I6<>"", J6<>""), J6-I6, )

enter image description here


1 Answer 1


=ARRAYFORMULA({IF((I6:I<>"") * (J6:J<>"") * (H6:H=K$5), TEXT(J6:J-I6:I, "hh:mm"), ), 
               IF((I6:I<>"") * (J6:J<>"") * (H6:H=L$5), TEXT(J6:J-I6:I, "hh:mm"), )})


demo spreadsheet

  • Could you share your email so I can share this with you? When copying and Pasting it doesn't seem to work... Then you will see where I have gone wrong May 5, 2019 at 20:32
  • you need to delete L6:L10
    – user0
    May 5, 2019 at 20:35
  • 1
    Got it! Thanks again for your time! +1 May 5, 2019 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.