How do I average time (in text format)
 

I have a big problem.

I have calculated multiple time values using the following example:
=TEXT(B2-B1,"h:mm") where B2 and B1 are different time values

I get an answer like 0:16 (indicating 0hr and 16min) and 1:14 for example..

Here is my question. I have tried to average the answers (like the average
of 0:16 and 1:14) and I get #DIV/0 returned. It seems that I cannot use
theAVERAGE function to perform this because it does not recognize the values
0:16 and 1:14. I think it is because they are in TEXT format. Is this
correct?

If so is there any way that I can fix this? Because I have multiple values
over multiple worksheets and would like to average them all. It would be
extremely difficult and time consuming to use the VALUE function to change
all of the answers because I would have to do this to each answer
individually. A second question is, does anyone know how to apply the VALUE
function over multiple sheets.

Thanks so much

How do I average time (in text format)
 

Maybe...
=b2-b1
but use a custom format of h:mm

or

=--TEXT(B2-B1,"h:mm")
and use a custom format of h:mm



Butterycolor wrote:

I have a big problem.

I have calculated multiple time values using the following example:
=TEXT(B2-B1,"h:mm") where B2 and B1 are different time values

I get an answer like 0:16 (indicating 0hr and 16min) and 1:14 for example..

Here is my question. I have tried to average the answers (like the average
of 0:16 and 1:14) and I get #DIV/0 returned. It seems that I cannot use
theAVERAGE function to perform this because it does not recognize the values
0:16 and 1:14. I think it is because they are in TEXT format. Is this
correct?

If so is there any way that I can fix this? Because I have multiple values
over multiple worksheets and would like to average them all. It would be
extremely difficult and time consuming to use the VALUE function to change
all of the answers because I would have to do this to each answer
individually. A second question is, does anyone know how to apply the VALUE
function over multiple sheets.

Thanks so much

--

Dave Peterson