ddd:hh:mm need calculate average days

[tndanley74]

I have a system that's exporting the time between the time a specimen is collect to the time the result is received. its showing info such as :

[TABLE="width: 163"]
<colgroup><col></colgroup><tbody>[TR]
[TD]000:05:08
[/TD]
[/TR]
[TR]
[TD]000:05:08[/TD]
[/TR]
[TR]
[TD]000:04:30
[/TD]
[/TR]
[TR]
[TD]000:04:24[/TD]
[/TR]
[TR]
[TD]000:03:54
[/TD]
[/TR]
[TR]
[TD]000:03:44[/TD]
[/TR]
[TR]
[TD]000:02:46
[/TD]
[/TR]
</tbody>[/TABLE]

I need to be able to calculate average days hours minutes from this. But the =Average() function does not work. Can someone provide suggestions.

 

[Comfy]

try this:

Excel 2010
	A	B
2	000:05:08	05:08:00
3	000:05:08	05:08:00
4	000:04:30	04:30:00
5	000:04:24	04:24:00
6	000:03:54	03:54:00
7	000:03:44	03:44:00
8	000:02:46	02:46:00
9		04:13:26
Sheet1

Cell Formulas
Range	Formula
B2	=TIMEVALUE(RIGHT(A2,5))
B9	=AVERAGE(B2:B8)

 

[AlphaFrog]

Excel doesn't recognize this format ddd:hh:mm as a days or time. It could be converted though.

If all the days are zero (000: ) in your data, Select the data column and use the Replace (Ctrl+H) to replace 000: with nothing. That should convert everything to hh:mm time that Excel recognizes. You can then average the time.

Otherwise if the data has days (001: ), use a formula like this to convert it to days-time
=LEFT(A1,3) + RIGHT(A1,5)
Format the cells with the formulas as Custom: d hh:mm

 

[tndanley74]

Alpha frog that works great! The only hiccup is if I have more than 31 days in the ddd: part.. it does something funky

040:05:08 --> 30 05:08

(comfy I couldn't get yours to work because its not all zero for days)

Excel doesn't recognize this format ddd:hh:mm as a days or time. It could be converted though.

If all the days are zero (000: ) in your data, Select the data column and use the Replace (Ctrl+H) to replace 000: with nothing. That should convert everything to hh:mm time that Excel recognizes. You can then average the time.

Otherwise if the data has days (001: ), use a formula like this to convert it to days-time
=LEFT(A1,3) + RIGHT(A1,5)
Format the cells with the formulas as Custom: d hh:mm

 

[AlphaFrog]

Alpha frog that works great! The only hiccup is if I have more than 31 days in the ddd: part.. it does something funky

040:05:08 --> 30 05:08

Yes, that's a limitation of days-time cell formatting in Excel. It can't display days-time > 31 days. The cell value is correct though despite the inaccurate formatting displayed. You could use another cell to display days-time as text.
=INT(B1)&TEXT(B1,":hh:mm")

Or just display the days-time as a decimal number where integer is days and the decimal portion is time.