Converting duration in NUMBER format to duration in TIME format in Excel.

[judejerrysalmon]

Dear all,

I have the duration data for more than 40000 rows in the number format. I need a method to convert the same into time format in excel. BY saying number format i mean 1 Hours & 45 minutes has been given as 1.45. I would like to have the same value 1.45 in the TIME format in excel so that i can sum or average the overall values correctly.

Sample data

[TABLE="width: 48"]


<colgroup><col style="width: 48pt;" width="64">
<tbody>[TR]

[TD="class: xl63, width: 64, bgcolor: transparent"]7.00[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]30.00[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]0.30[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]1.15[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]2.15[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]6.30[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]0.03[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]2.50[/TD]

[/TR]


</tbody>[/TABLE]


Thanks,

 

[shg]

As has appeared several times in this thread, the result MUST be formatted as [h]:mm, not h:mm, in order to display hours > 24.

 

[etaf]

format the cell [h]:mm
works ok for 30 hrs and 50.50

 

[judejerrysalmon]

Wow.. Finally, it's done.. Thanks a ton folks pgc01,etaf and shg... pgc01 - Amazing stuff... Thanks a lot.Keep going.

 

[pgc01]

You're welcome. I'm glad we could help.