# Numbers to time( hh:mm).

Posted by Brian on June 20, 2000 5:40 AM

A quicky.

Can someone tell me how to convert digits to time (hh:mm).

i.e. 955 =09:55, 1043 =10:43, 1910=19:10 etc.

Thanks.

Posted by Brian on June 21, 0100 12:39 AM

Thanks & is there a formula also?

Application.ScreenUpdating = False x = 0 Do x = x + 1 Time = Cells(x, 1).Value If Len(Time) > 3 Then Cells(x, 2).Value = Left(Time, 2) & ":" & Right(Time, 2) ElseIf Len(Time) = 3 Then With Cells(x, 2) .Value = "0" & Left(Time, 1) & ":" & Right(Time, 2) .NumberFormat = "hh:mm" End With End If Loop While Cells(x + 1, 1).Value <> "" Application.ScreenUpdating = True

Ryan.

It works really well, (perfect)

Thanks.

Just out of curiosity is there a formula that can add the : in the middle?

Brian

Posted by mads on June 21, 0100 5:21 AM

Re: Thanks & is there a formula also?

Application.ScreenUpdating = False x = 0 Do x = x + 1 Time = Cells(x, 1).Value If Len(Time) > 3 Then Cells(x, 2).Value = Left(Time, 2) & ":" & Right(Time, 2) ElseIf Len(Time) = 3 Then With Cells(x, 2) .Value = "0" & Left(Time, 1) & ":" & Right(Time, 2) .NumberFormat = "hh:mm" End With End If Loop While Cells(x + 1, 1).Value <> "" Application.ScreenUpdating = True

=IF(LEN(A1)=3,LEFT(A1,1)&":"& RIGHT(A1,2),LEFT(A1,2)&":"& RIGHT(A1,2))

mads

Posted by Brian on June 21, 0100 6:44 AM

Cheers guys

Cheers,

You've both helped me out of a tight spot.

Brian.

Application.ScreenUpdating = False x = 0 Do x = x + 1 Time = Cells(x, 1).Value If Len(Time) > 3 Then Cells(x, 2).Value = Left(Time, 2) & ":" & Right(Time, 2) ElseIf Len(Time) = 3 Then With Cells(x, 2) .Value = "0" & Left(Time, 1) & ":" & Right(Time, 2) .NumberFormat = "hh:mm" End With End If Loop While Cells(x + 1, 1).Value <> "" Application.ScreenUpdating = True

Posted by Ryan on June 20, 0100 10:16 AM

This will take the number in A1 and put the time

' in B1. Change it to your needs, if it has to loop

' through a selection or whatever. Hope this helps.

' Ryan

Sub NumtoDate()

Dim Time As String

Time = Range("A1").Value

If Len(Time) > 3 Then

Range("B1").Value = Left(Time, 2) & ":" & Right(Time, 2)

ElseIf Len(Time) = 3 Then

Range("B1").Value = "0" & Left(Time, 1) & ":" & Right(Time, 2)

Range("B1").NumberFormat = "hh:mm"

End If

End Sub

Posted by Ryan on June 20, 0100 10:26 AM

Looping through cells

Here is the same code that will loop through the cells in column A, starting at A1 and put the time in Column B to the corresponding row. This will loop until the cell in column A is empty.

Hope you like it.

Ryan

Sub NumtoDate()

Dim Time As String

Dim x As Integer

Application.ScreenUpdating = False

x = 0

Do

x = x + 1

Time = Cells(x, 1).Value

If Len(Time) > 3 Then

Cells(x, 2).Value = Left(Time, 2) & ":" & Right(Time, 2)

ElseIf Len(Time) = 3 Then

With Cells(x, 2)

.Value = "0" & Left(Time, 1) & ":" & Right(Time, 2)

.NumberFormat = "hh:mm"

End With

End If

Loop While Cells(x + 1, 1).Value <> ""

Application.ScreenUpdating = True

End Sub