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,

 

[pgc01]

Welcome to the board

The unit time in excel is 1 day, which means that you just have to divide the numbers you have by 24 and format the cell as time.

For ex., write in a cell 24, copy, select the target cells and PasteSpecial->Divide. Format the cells.

 

[etaf]

try
=TIMEVALUE(LEFT(A1,LEN(A1)-3)&":"&RIGHT(A1,2))
and format cells as time
this assumes its exactly as shown with zeros , ie always 2 decimal places , even if zero ie .00
and not a decimal fraction after the . - as you say .45 is not decimal but 45 mins - so you would never have over .59

 

[judejerrysalmon]

try
=TIMEVALUE(LEFT(A1,LEN(A1)-3)&":"&RIGHT(A1,2))
and format cells as time
this assumes its exactly as shown with zeros , ie always 2 decimal places , even if zero ie .00
and not a decimal fraction after the . - as you say .45 is not decimal but 45 mins - so you would never have over .59

Thanks a lot etaf for your effort. You have understood the problem perfectly. BUt, this formula returns correct values fot 35 of 79 instances. It is returning #VALUE error for the remaining.

First column is the raw data and second column is the output after applying the formula.[TABLE="width: 413"]
<tbody>[TR]
[TD]
Duration in number format[/TD]
[TD]Duration in TIME format (h:mm)[/TD]
[/TR]
[TR]
[TD]0.03[/TD]
[TD="align: right"]0:03[/TD]
[/TR]
[TR]
[TD]0.05[/TD]
[TD="align: right"]0:05[/TD]
[/TR]
[TR]
[TD]0.06[/TD]
[TD="align: right"]0:06[/TD]
[/TR]
[TR]
[TD]0.07[/TD]
[TD="align: right"]0:07[/TD]
[/TR]
[TR]
[TD]0.08[/TD]
[TD="align: right"]0:08[/TD]
[/TR]
[TR]
[TD]0.09[/TD]
[TD="align: right"]0:09[/TD]
[/TR]
[TR]
[TD]0.10[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]0.11[/TD]
[TD="align: right"]0:11[/TD]
[/TR]
[TR]
[TD]0.12[/TD]
[TD="align: right"]0:12[/TD]
[/TR]
[TR]
[TD]0.13[/TD]
[TD="align: right"]0:13[/TD]
[/TR]
[TR]
[TD]0.14[/TD]
[TD="align: right"]0:14[/TD]
[/TR]
[TR]
[TD]0.15[/TD]
[TD="align: right"]0:15[/TD]
[/TR]
[TR]
[TD]0.16[/TD]
[TD="align: right"]0:16[/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]0.17[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]0.18[/TD]
[TD="align: right"]0:18[/TD]
[/TR]
[TR]
[TD]0.20[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]0.21[/TD]
[TD="align: right"]0:21[/TD]
[/TR]
[TR]
[TD]0.22[/TD]
[TD="align: right"]0:22[/TD]
[/TR]
[TR]
[TD]0.23[/TD]
[TD="align: right"]0:23[/TD]
[/TR]
[TR]
[TD]0.24[/TD]
[TD="align: right"]0:24[/TD]
[/TR]
[TR]
[TD]0.25[/TD]
[TD="align: right"]0:25[/TD]
[/TR]
[TR]
[TD]0.26[/TD]
[TD="align: right"]0:26[/TD]
[/TR]
[TR]
[TD]0.27[/TD]
[TD="align: right"]0:27[/TD]
[/TR]
[TR]
[TD]0.30[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]0.35[/TD]
[TD="align: right"]0:35[/TD]
[/TR]
[TR]
[TD]0.38[/TD]
[TD="align: right"]0:38[/TD]
[/TR]
[TR]
[TD]0.40[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]0.41[/TD]
[TD="align: right"]0:41[/TD]
[/TR]
[TR]
[TD]0.44[/TD]
[TD="align: right"]0:44[/TD]
[/TR]
[TR]
[TD]0.45[/TD]
[TD="align: right"]0:45[/TD]
[/TR]
[TR]
[TD]0.50[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]0.55[/TD]
[TD="align: right"]0:55[/TD]
[/TR]
[TR]
[TD]1.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]1.10[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]1.15[/TD]
[TD="align: right"]1:15[/TD]
[/TR]
[TR]
[TD]1.17[/TD]
[TD="align: right"]1:17[/TD]
[/TR]
[TR]
[TD]1.20[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]1.26[/TD]
[TD="align: right"]1:26[/TD]
[/TR]
[TR]
[TD]1.30[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]1.35[/TD]
[TD="align: right"]1:35[/TD]
[/TR]
[TR]
[TD]1.40[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]1.49[/TD]
[TD="align: right"]1:49[/TD]
[/TR]
[TR]
[TD]1.50[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]1.52[/TD]
[TD="align: right"]1:52[/TD]
[/TR]
[TR]
[TD]1.55[/TD]
[TD="align: right"]1:55[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]2.15[/TD]
[TD="align: right"]2:15[/TD]
[/TR]
[TR]
[TD]2.20[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]2.30[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]2.50[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]3.30[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]3.45[/TD]
[TD="align: right"]3:45[/TD]
[/TR]
[TR]
[TD]3.50[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]4.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]4.30[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]5.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]6.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]6.30[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]7.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]8.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]9.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]10.50[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]12.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]14.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]16.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]18.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]20.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]21.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]24.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]28.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]30.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]32.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]40.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]44.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]190.00[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]


Thanks,

 

[pgc01]

I see, with 0.25 you don't mean 0.25 of an hour, you mean 0:25 in excel time.

In that case try:

=DOLLARDE(A1,60)/24

and format the cell as time.

 

[etaf]

strange, not sure why - but pgc01 has replied
and i have learnt a new function from pgc01
thanks

you may need to format as [h]:mm

 

[judejerrysalmon]

I see, with 0.25 you don't mean 0.25 of an hour, you mean 0:25 in excel time.

In that case try:

=DOLLARDE(A1,60)/24

and format the cell as time.

Thanks for your effort pgc01. In my data, the duration column is in NUMBER format (thats the way it has been downloaded). So,if it is 0.25, it means 25 minutes. If it is 1.45, it means 1 hour and 45 minutes. The colmun containing these duration values are in NUMBER format and not in TIME format. 1.45 in my data is not the decimal equivalent as well. 1.45 in my data is literally the exact duration, unfortunately displayed in number format.

Thanks

 

[judejerrysalmon]

strange, not sure why - but pgc01 has replied
and i have learnt a new function from pgc01
thanks

you may need to format as [h]:mm

Thanks etaf. I have formatted the output column in [h]:mm format only. As your formula works for 35 instances out of the 79, appreciate if you can tweak the same if possible. I am pretty new when it comes to formulas that requires higher order customization. Thanks in advance.

 

[etaf]

if the cells is in number format
then
7.00
should display as 7
unless you have formatted for 2 decimals

because it was left justified , then text

what happened using DOLLARDE(A1,60)/24
should work for numbers and text

 

[judejerrysalmon]

I see, with 0.25 you don't mean 0.25 of an hour, you mean 0:25 in excel time.

In that case try:

=DOLLARDE(A1,60)/24

and format the cell as time.

Thanks pgc01... This formula works well for values upto 24.. For duration 24 and above, it is returning wrong values.. For 24 it is returning 0:00, for 50 hours, it's returning 2:00.. Any possibilities of tweaking so that this returns correct values for 24 and above as well?. Thanks in advance.