In columns K and L I have time stamps.

Let's say 03:30 is in column K and 04:30 is in column L.

I want the total elapsed amount of time in hh:mm in column P.

I have tried the average function, but it's not coming out correctly.

  • 1
    What format (the "123" toolbar icon) are the cells set to, and what does "not coming out correctly" mean? If you create a spreadsheet, putt 04:30 and 03:30 in cells A1 and A2, and =AVERAGE(A1,A2) in A3, what do you get and what do you expect to get?
    – Gabe
    May 3, 2013 at 15:14

2 Answers 2


For times that span past midnight you cannot simply subtract. Or you'll get a negative number.

9:00 PM  

5:38 AM 


What you'll want to do (where start is column A and End is column B, etc.) is the following formula: =(B2-A2)+1

The one being equal to twenty-four hours. So: 9 pm to 5:38 am = 8:38.


Do you mean output like this!

Cell C1 shows correct averages.

If yes, set the cell format to 24 hours and use the Average for (Numbers)

enter image description here

For time Elapsed use simple subtraction = Cell B - Cell A

enter image description here

  • I apologize, I want the elapsed amount of time to show in C1. So the clock starts in A1 and stops in B1. I did format the cells.
    – flipnija513
    May 3, 2013 at 16:17
  • 1
    That's not an average, that's simple subtraction.
    – ale
    May 3, 2013 at 17:49
  • 1
    Your question says Average. If you want the Elapsed time just subtract.
    – Ruskes
    May 3, 2013 at 18:36

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.