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 :

000:05:08

000:05:08

000:04:30

000:04:24

000:03:54

000:03:44

000:02:46

<colgroup><col></colgroup><tbody>
</tbody>

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.