Average Elapsed Time

New Contributor


Excel 2013.  All cells in this spreadsheet are formatted as Time. 

Column I is the End Time. Column E is the Start Time. 

Column F includes minutes to deduct from the Elapsed time.

Column H includes minutes to deduct from the Elapsed time.

Column G contains no data.


Column J contains cells with this formula to calculate the Elapsed Time:







How can I calculate the average of the elapsed times in Column J?

6 Replies




Adjust the range J16:J18; it does not matter if it includes empty cells.

Thanks Hans.

=AVERAGE(J16:J18) produces 13:45 on my computer.
The average of the hours is 5:45.
best response confirmed by Hans Vogelaar (MVP)


You may simplify a bit using formulae as


Average returns correct results, please open attached. Perhaps you may share sample file?


Thanks so much Sergei. Someone at the Excel Help Forum gave me a similar answer. Your solution is very good. I have not seen the MOD function used with the "-1". How does that work?


Let's take E18 = 23:00 and I18 = 09:00 as example.


I18-E18 = 9:00 - 23:00 = -14:00


Excel stores times internally as numbers, with 1 day = 24 hours as unit, so for example 6:00 is stored as  0.25 and 12:00 is stored as 0.5.

The MOD function calculates the remainder after division.

For example MOD(17,5) returns 2 because 17 goes 3 times into 5, leaving 2.

And MOD(-14,24) returns 10 because -14 goes -1 times into 24, leaving 10 (i.e. -14 = -1*24+10).

Similarly, MOD(-14:00, 1) returns 10:00 since 1 is equivalent to 24 hours.

Thanks Hans.