VBA Text to Time

[tezza]

Hi all,

I got this report that comes from an external software which is showing time as text in a 12 hour format, eg: 04:30 PM

I can add a formula to change it so that it works = TimeValue(C2)

How do I do this in VBA:

• Insert Col at Col D
• Cell D1 = Time
• Cells D2 down = TimeValue(C2) if C2 isn't blank (drag down until it gets to the bottom row
• Change Range Col D2 down to Time Format [hh]:mm

Note: Col A always has data in it to know when to stop dragging the formula down.

In a formula this works, but other people use it that would likely mess it up, so I'll run it as a macro.

IF(C2="","",TIMEVALUE(C2))

EG:

Staff_Mileage (2).xlsx
	C	D
1	Start Time	Time
2
3
4
5
6
7
8	08:25 AM	08:25
9
10
11	09:00 AM	09:00
12	09:00 AM	09:00
13
14
15	04:30 PM	16:30
Staff_Mileage

Cell Formulas
Range	Formula
D2:D15	D2	=IF(C2="","",TIMEVALUE(C2))

 

[saboh12617]

Hello,
I think the macro below should do the work:

Public Sub AddTimeCol()
  Dim colA As Variant
  With ThisWorkbook.Worksheets("Staff_Mileage")
    .Columns("B").Insert xlShiftToRight
    .Range("B1").Value2 = "Time"
    colA = Range(.Range("A2"), .Cells(.Rows.Count, 1).End(xlUp)).Value2
  End With
  
  Dim i As Long
  On Error Resume Next
  For i = LBound(colA, 1) To UBound(colA, 1)
    If IsEmpty(colA(i, 1)) Then
      colA(i, 1) = ""
    Else
      colA(i, 1).Value2 = VBA.TimeValue(colA(i, 1))
    End If
  Next i
  On Error GoTo 0
  
  With ThisWorkbook.Worksheets("Staff_Mileage").Range("B2").Resize(UBound(colA) - LBound(colA) + 1)
    .Value2 = colA
    .NumberFormat = "[hh]:mm"
  End With
End Sub

 

[tezza]

Hiya, thank you for your reply.

Something is not quite right though as it's pulling date and time?

 

[saboh12617]

Oh that's curious, i did not have this on my test.

What if you edit the code like so:

Public Sub AddTimeCol()
  Dim colA As Variant
  With ThisWorkbook.Worksheets("Staff_Mileage")
    .Columns("B").Insert xlShiftToRight
    .Range("B1").Value2 = "Time"
    colA = Range(.Range("A2"), .Cells(.Rows.Count, 1).End(xlUp)).Value2
  End With
  
  Dim i As Long
  On Error Resume Next
  For i = LBound(colA, 1) To UBound(colA, 1)
    If IsEmpty(colA(i, 1)) Then
      colA(i, 1) = ""
    Else
      colA(i, 1).Value2 = TimeSerial(Hour(colA(i, 1)), Minute(colA(i, 1)), 0)
    End If
  Next i
  On Error GoTo 0
  
  With ThisWorkbook.Worksheets("Staff_Mileage").Range("B2").Resize(UBound(colA) - LBound(colA) + 1)
    .Value2 = colA
    .NumberFormat = "[hh]:mm"
  End With
End Sub

 

[tezza]

Oh that's curious, i did not have this on my test.

What if you edit the code like so:

Public Sub AddTimeCol()
  Dim colA As Variant
  With ThisWorkbook.Worksheets("Staff_Mileage")
    .Columns("B").Insert xlShiftToRight
    .Range("B1").Value2 = "Time"
    colA = Range(.Range("A2"), .Cells(.Rows.Count, 1).End(xlUp)).Value2
  End With
 
  Dim i As Long
  On Error Resume Next
  For i = LBound(colA, 1) To UBound(colA, 1)
    If IsEmpty(colA(i, 1)) Then
      colA(i, 1) = ""
    Else
      colA(i, 1).Value2 = TimeSerial(Hour(colA(i, 1)), Minute(colA(i, 1)), 0)
    End If
  Next i
  On Error GoTo 0
 
  With ThisWorkbook.Worksheets("Staff_Mileage").Range("B2").Resize(UBound(colA) - LBound(colA) + 1)
    .Value2 = colA
    .NumberFormat = "[hh]:mm"
  End With
End Sub

Have you factored in that I'm referring to columns C and D, not A and B? Col A is purely to know how many rows are populated.

 

[saboh12617]

No indeed, i was working with Col A & B. This should be better

Public Sub AddTimeCol()
  Dim colIni As Variant
  With ThisWorkbook.Worksheets("Staff_Mileage")
    .Columns("D").Insert xlShiftToRight
    .Range("C1").Value2 = "Time"
    colIni = Range(.Range("C2"), .Cells(.Rows.Count, 3).End(xlUp)).Value2
  End With
 
  Dim i As Long
  On Error Resume Next
  For i = LBound(colIni, 1) To UBound(colIni, 1)
    If IsEmpty(colIni(i, 1)) Then
      colIni(i, 1) = ""
    Else
      colIni(i, 1).Value2 = TimeSerial(Hour(colIni(i, 1)), Minute(colIni(i, 1)), 0)
    End If
  Next i
  On Error GoTo 0
 
  With ThisWorkbook.Worksheets("Staff_Mileage").Range("C2").Resize(UBound(colIni) - LBound(colIni) + 1)
    .Value2 = colIni
    .NumberFormat = "[hh]:mm"
  End With
End Sub

 

[tezza]

No indeed, i was working with Col A & B. This should be better

Public Sub AddTimeCol()
  Dim colIni As Variant
  With ThisWorkbook.Worksheets("Staff_Mileage")
    .Columns("D").Insert xlShiftToRight
    .Range("C1").Value2 = "Time"
    colIni = Range(.Range("C2"), .Cells(.Rows.Count, 3).End(xlUp)).Value2
  End With
 
  Dim i As Long
  On Error Resume Next
  For i = LBound(colIni, 1) To UBound(colIni, 1)
    If IsEmpty(colIni(i, 1)) Then
      colIni(i, 1) = ""
    Else
      colIni(i, 1).Value2 = TimeSerial(Hour(colIni(i, 1)), Minute(colIni(i, 1)), 0)
    End If
  Next i
  On Error GoTo 0
 
  With ThisWorkbook.Worksheets("Staff_Mileage").Range("C2").Resize(UBound(colIni) - LBound(colIni) + 1)
    .Value2 = colIni
    .NumberFormat = "[hh]:mm"
  End With
End Sub

Works like a dream, thank you

 

[saboh12617]

Thanks for the feedback, have a good day!