MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HH:MM code


Posted by May on July 12, 2000 8:28 AM

How can you change this code to look into say column C and change all numbers in Column D to the HH:MM format i.e. inserting the :

This code only does Column A

Cheers

May

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


Posted by May on July 13, 0100 1:56 AM

Error

I'm sorry.

I don't know why but it seems to be looking for something in Column A (I accidently left the same data there and I thought it did Column C but didn't).

I've copied the code but as I said it's looking for something in Col A

Any thoughts?

May

Posted by Ivan Moala on July 13, 0100 2:23 AM

Re: Error

May
Change line that reads;
Loop While Cells(x + 1, 1).Value <> "" TO
Loop While Cells(x + 1, 3).Value <> ""


Ivan

Posted by Ryan on July 13, 0100 5:26 AM

Thanks Ivan

Skipped right over that last one. SOrry about that.

Posted by Ivan Moala on July 13, 0100 6:16 AM

Re: Thanks Ivan

Ryan
Thats easy to do....I do it a lot :-) especially
if you answer a lot of Q as you do.

Ivan

Posted by Ryan on July 12, 0100 8:36 AM

May,
This will do column C into D. Whereever it says Cells(x,1), the "1" is designating what column to look in or put a value into. So 3 = C and 4 = D, you can change this however you need it. Let me know if this is what you want!

Ryan

Dim Time As String
Dim x As Integer

Application.ScreenUpdating = False
x = 0
Do
x = x + 1
Time = Cells(x, 3).Value

If Len(Time) > 3 Then
Cells(x, 4).Value = Left(Time, 2) & ":" & Right(Time, 2)
ElseIf Len(Time) = 3 Then
With Cells(x, 4)
.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 May on July 12, 0100 9:00 AM

Thank you for your speedy response.

May.