We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Time average

asked 2020-02-29 19:32:17 +0200

PaulaO gravatar image

updated 2020-02-29 19:36:26 +0200

I have a column of time spans. For example 8:45:06 12:35:45 etc

I'd like to determine the average time span. Like, what is the average amount of hours I spent doing X each day. Not what average time I started X (although I have that data) but time spent.

=AVERAGE(A1:A2) gives 0.
=AVERAGE(A1:A2)/24 gives #DIV/0! (read here about how spreadsheets see time as % of days vs hours)
=SUM((A1:A2)/2 gives 0 (this is the formula used for the other columns that are just numbers)

Numbers are formated as time 00:00:00 without the PM because it isn't a clock time but an amount of time.

Surely I don't have to do /24 for each entry. That would be time consuming. And I'd have to do another average listing. ;)

edit retag flag offensive close merge delete


Usually it's matter of having the values as text. Select a couple of cells or the column, and use Menu/Data/Text to convert in true time values.

m.a.riosv gravatar imagem.a.riosv ( 2020-02-29 20:23:20 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-02-29 21:07:28 +0200

Inclement gravatar image

updated 2020-02-29 22:46:50 +0200

Just plain average worked for me with no trouble.

Maybe start fresh with the formats in case something got messed up? Use right-click > Clear Contents... > Formats on all your time cells, and then re-do the time formats. (Edit: if this is a large spreadsheet, maybe experiment with a small area first.) It sounds like you picked the correct ones, but if it helps here are the ones I used:

Format for A2:B4 - Category:Time, Format code HH:MM:SS AM/PM

Format for D2:D4 and D6 - Category:Time, Format code HH:MM:SS

        A               B           C                  D
1  Start time      Stop time     Formula            Result
2  04:00:00 PM     05:00:00 PM   =B2-A2             01:00:00
3  03:00:00 AM     10:00:00 AM   =B3-A3             07:00:00 
4  11:00:00 AM     01:00:00 PM   =B4-A4             02:00:00
6                  Average:      =AVERAGE(D2:D4)    03:20:00

The average of 1, 7, and 2 is 3.3333, so the result is correct.

edit flag offensive delete link more


Thanks! I'll give that a try.

PaulaO gravatar imagePaulaO ( 2020-03-18 23:37:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-02-29 19:32:17 +0200

Seen: 381 times

Last updated: Feb 29 '20