Convert General Format To Time

[dgrowe]

I have some data being pulled in by a query program from an AS/400 system. Unfortunately the times I am pulling in are not in a numeric format. The numbers below represent actual times in 24-hour format (ex. 21:30, 2:13, 4:45, & 22:24). I would like to change them to a decimal time format (ex. 21.50, 2.22, 4.75, & 22.4).

213000
21300
44500
222400

 

[schielrn]

Format the cell to number and try this:

=TIME(IF(LEN(A25)=6,LEFT(A25,2),LEFT(A25,1)),IF(LEN(A25)=6,MID(A25,3,2),MID(A25,2,2)),RIGHT(A25,2))*24

Change A25 to appropriate cell. You may have to after inputting the formual change to number, but not sure.

Hope that helps.

Book1
	A	B	C	D
25	213000	21.50
26	21300	2.22
27	44500	4.75
28	222400	22.40
Sheet1

 

[dgrowe]

That worked great! Thanks.

 

[lenze]

Here is some code to automate this

Sub ConvertTime()
Dim cl As Range
For Each cl In Range("$A$2:$A" & Range("$A$65536").End(xlUp).Row)
cl = cl * 0.0001
cl = Evaluate("=dollarde(" & cl & ",60)")
Next cl
End Sub

Format cells as desired
Analysis ToolPak must be installed!!!

lenze

 

[Scott Huish]

Another formula possibility:

=TEXT(A1,"00\:00\:00")*24

Format as number with 2 decimal places.


<SCRIPT language=JavaScript src="<a href=" popup.js" colo puremis sun www.interq.or.jp http:></SCRIPT><CENTER><TABLE" target="_blank">

Excel Workbook
	A	B	C
1	213000		21.50
2	21300		2.22
3	44500		4.75
4	222400		22.40
Sheet1

</CENTER>