Converting Number to Time Format

[Justinian]

I have a worksheet with 747 rows of data. In column E, starting with E3, are times that have been converted to numbers (e.g., 11:00 is 11.00). I need to convert all the numbers to times but when I try TEXT, I get the wrong result. In E3 is 11.00 and when I apply =TEXT(E3,"H:MM"), I get 0:00. When I apply the same formula to 10.50, I get 12:00. I cannot figure out how to fix this.

 

[mole999]

the decimal says to the system, the bit to the left is whole days. there is a way of doing it

On a COPY do a find replace . to : and see if that makes it work

 

[Justinian]

Find and replace did not work. All nu ber with a decimal stayed that way but numbers like 10.50 became 0.42.

 

[Dave Patton]

try

Excel 2010
	E	F
2	11.00	11:00 AM
3	10.50	10:30 AM
4
3a

Cell Formulas
Range	Formula
F2	=TIME(INT(E2),MOD(E2,1)*60,0)
F3	=TIME(INT(E3),MOD(E3,1)*60,0)

 

[Scott Huish]

If 10.5 is supposed to be 10:30 then all you have to do is divide by 24
Format result cells as time:

Excel 2010
	A	B
1	11.00	11:00 AM
2	10.50	10:30 AM
Sheet1

Cell Formulas
Range	Formula
B1	=A1/24
B2	=A2/24

If however 10.50 is supposed to be 10:50 AM, then perhaps:
=SUBSTITUTE(A1,".",":")+0
Format as time.

Excel 2010
	A	B
1	11.00	11:00 AM
2	10.50	10:50 AM
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,".",":")+0
B2	=SUBSTITUTE(A2,".",":")+0

 

[AlKey]

Try this
=DOLLARDE(A1,60)/24

Book1
	A	B
1	11.00	11:00 AM
2	11.50	11:50 AM
Sheet1

 

[Justinian]

try
Excel 2010

	E	F

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]11:00 AM[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10.50[/TD]
[TD="align: right"]10:30 AM[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

3a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=TIME(INT(E2),MOD(E2,1)*60,0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=TIME(INT(E3),MOD(E3,1)*60,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That works! Thank you.

 

[Justinian]

If 10.5 is supposed to be 10:30 then all you have to do is divide by 24
Format result cells as time:

Excel 2010

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]11:00 AM[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10.50[/TD]
[TD="align: right"]10:30 AM[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=A1/24[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=A2/24[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If however 10.50 is supposed to be 10:50 AM, then perhaps:
=SUBSTITUTE(A1,".",":")+0
Format as time.

Excel 2010

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]11.00[/TD]
[TD="align: right"]11:00 AM[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10.50[/TD]
[TD="align: right"]10:50 AM[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUBSTITUTE(A1,".",":")+0[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SUBSTITUTE(A2,".",":")+0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes, 10.50 is supposed to be 10:30 but both your solutions worked. I did not even think about dividing by 24, which is the easiest solution.

Thank you.