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
2	11.00	11:00 AM
3	10.50	10:30 AM
4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

3a​

Worksheet Formulas Cell Formula F2 =TIME(INT(E2),MOD(E2,1)*60,0) F3 =TIME(INT(E3),MOD(E3,1)*60,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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
1	11.00	11:00 AM
2	10.50	10:30 AM

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =A1/24 B2 =A2/24 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =SUBSTITUTE(A1,".",":")+0 B2 =SUBSTITUTE(A2,".",":")+0 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.