Convert Date and Time Text to an Excel Date/Time Serial Number

[wdgor]

Any ideas on how I could convert this text string into a date/time serial number in Excel? Thanks for any assistance.

12:09:01AM Jun 18, 2022

 

[wdgor]

@wdgor - You are supposed to mark the message with the solution in it that you are going to use as the Answer, not your message saying ithat you have a solution.

OK - I changed it. Thanks again.

 

[GraH]

This looks like a really elegant solution but I have never used power query. I have Excel 365 and power query does show up under the data tab. When I select it and try to use data sources in the current workbook, I get "We didn't find any data sources in this workbook". I originally tried to parse out the data table (see below) thinking that I might be able to do something with it - no luck. Perhaps I need to do some YouTube investigation on how to use power query.

Select the data, on Get & Transform choose from table/range.
Confirm my data has headers.
You can also do this in another workbook, same routine but select data from file from workbook.

 

[Macropod]

With a simple macro:

Sub Demo()
Application.ScreenUpdating = False
Dim r As Long
With ActiveSheet
  For r = 1 To .UsedRange.Rows.Count
    .Cells(r, 2).Value = CDate(.Cells(r, 1).Text)
  Next
  .Columns(2).NumberFormat = "0.000000"
End With
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

With a simple macro:

Sub Demo()
Application.ScreenUpdating = False
Dim r As Long
With ActiveSheet
  For r = 1 To .UsedRange.Rows.Count
    .Cells(r, 2).Value = CDate(.Cells(r, 1).Text)
  Next
  .Columns(2).NumberFormat = "0.000000"
End With
Application.ScreenUpdating = True
End Sub

The OP seems content with the formula solution I offered, but if a VBA solution was desired, I would probably do it this way (given the OP has told us the time values have leading zeros when the hours are single digits) since it would eliminate the loop...

Sub Demo()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Offset(, 1) = Evaluate(Replace("0+MID(@&"" ""&REPLACE(@,9,0,"" ""),12,LEN(@)+1)", "@", .Address))
  End With
End Sub

 

[wdgor]

Thanks all but I have implemented Rick’s approach and it works perfectly.