Average of Time

[theath]

I have a column of Days:Hours that I am trying to average. I keep getting a DIV/0 error. I have tried Text to Columns wizard with no luck. Format of columns is ddd:hh


010:22

014:02

002:21

000:03

001:23

000:01

000:00

004:23

006:23

001:21

008:05

012:20

000:02

 

[NARAYANK991]

Hi ,


The problem is the extra 0 in the beginning. Because of this , the data is not being recognised as a numeric value.


Instead of entering 010:22 , enter 10:22 , which will be recognised as a number.


But even after this , the values will not be recognised the way you want them to be. If by 10:22 , you mean 10 days and 22 hours i.e. 262 hours , then you need to isolate the portions before and after the ":" , do the arithmetic , and then average the resulting values.


Narayan

 

[theath]

Unfortunately I can't control how the data is being exported.

 

[Hui]

I would add a helper column

I'll assume your data is in Column A

So in B2 use a formula like

=Left(A2,3)+Right(a2,2)/24

This will create a date.time number in the 1900 in Column B but don't worry about this

You can now use any maths on this and so =Average(B2:B100) etc is ok

 

[theath]

Hui,


This will work just fine. I am cleaning up date from a report output and only need the final numbers. This is perfect! Thanks!

 

[Hui]