MrExcel.com - Your One Stop for Excel Tips & Solutions

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