CODEWITHSUNDEEP
excelexcel-formula
warrenfitzhenry
warrenfitzhenry

Reputation: 2299

Excel average of values in time values

I have these values, which are time values. However, they are not formatted to time values, so an average will give me 8:82, when it should give 9:03. I can't seem to change the format into hh:mm, the values change to 00:00.

Current format is #0:\00

8:53    9:11

How do I get these recognized as time values?

Upvotes: 0

Views: 60

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

Assuming that 8:53 is in A1 and 9:11 is in B1 then you can use this formula:

=TEXT(AVERAGE(TIMEVALUE(A1),TIMEVALUE(B1)),"hh:mm")

Which actually gives an average of 9:02.

The Office documentation says:

Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).

So you wouldn't be able to do something like =TEXT(AVERAGE(TIMEVALUE(A1:D1)),"hh:mm")

Upvotes: 1

Related Questions