Imagine you have a Google sheet with two columns, each holding a time - let's say one is bus departure and the other is bus arrival.

Is there any elegant way to present the average of all the time the bus spent on the way each day?


Departure     Arrival     Average_Time_in_Transit
13:00         14:00       1:00
13:15         14:15
12:15         13:15
12:00         13:00
  • for a total of times or average per each row/time? – user0 May 2 '19 at 9:10
  • Per row, I think - How much time the bus spent traveling, on average, every day? If it traveled for exactly one hour every day the result should be one cell holding 1:00 – Mike Meyers May 2 '19 at 9:23

=ARRAYFORMULA(IF(LEN(A2:A), TEXT(B2:B-A2:A, "[h]:mm"), ))


=TEXT(AVERAGE(ARRAYFORMULA(IF(LEN(A2:A), B2:B-A2:A, ))), "[h]:mm:ss")


=ARRAYFORMULA(IF(LEN(A2:A), TEXT((B2:B-A2:A)/COUNTA(A2:A), "[h]:mm:ss"), ))


| improve this answer | |
  • This is awesome, thank you! Is there any way you know of to do this without creating a third column "C" of the daily time difference? – Mike Meyers May 2 '19 at 9:44
  • @MikeMeyers did you see the 2nd picture? (if you use 2nd formula, you dont need column C with differences at all) – user0 May 2 '19 at 9:45
  • 1
    I have actually missed it, and it is exactly what I was looking for! Thanks, @user(), you're awesome! – Mike Meyers May 2 '19 at 9:53
  • @MikeMeyers I also added a breakdown of the average per each row – user0 May 2 '19 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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