Need Help Converting Date & Time Stored as Text

[Donnie]

I need help converting the following to a date & time that excel will
recognize: Jan 1 2007 9:00:00:000AM

This data resides in column A and is from a data dump from a read only
database file. Excel reads it as text.

Thanks in advance for the help.

Donnie

 

[Rick Rothstein \(MVP - VB\)]

Try this code in your macro...

Dim ModifiedDateString As String
ModifiedDateString = Replace(Range("A1").Value, ":000", " ")
Range("A1").Value = DateValue(ModifiedDateString) & " " & _
TimeValue(ModifiedDateString)

Rick

 

[Donnie]

Try this code in your macro...

Dim ModifiedDateString As String
ModifiedDateString = Replace(Range("A1").Value, ":000", " ")
Range("A1").Value = DateValue(ModifiedDateString) & " " & _
                    TimeValue(ModifiedDateString)

Rick









- Show quoted text -

Works great, thanks! How can I convert the text to date/time for all
entries in column A?

 

[carlo]

Works great, thanks!  How can I convert the text to date/time for all
entries in column A?- Hide quoted text -

- Show quoted text -

That would be:

Dim ModifiedDateString As String

for i = 1 to cells(65536,1).end(xlup).row
ModifiedDateString = Replace(Range("A" & i).Value, ":000", " ")
Range("A" & i).Value = DateValue(ModifiedDateString) & " " & _
TimeValue(ModifiedDateString)
next i

hth
Carlo

 

[Donnie]

That would be:

Dim ModifiedDateString As String

for i = 1 to cells(65536,1).end(xlup).row
  ModifiedDateString = Replace(Range("A" & i).Value, ":000", " ")
  Range("A" & i).Value = DateValue(ModifiedDateString) & " " & _
                    TimeValue(ModifiedDateString)
next i

hth
Carlo- Hide quoted text -

- Show quoted text -

Thanks for the support!

This works just fine until I reach a cell with no data, i.e., empty.
Is there a way to modify the code so it will exclude/ignore empty
cells and continue converting the remainder of the column?

Donnie

 

[Rick Rothstein \(MVP - VB\)]

This works just fine until I reach a cell with no data, i.e., empty.
Is there a way to modify the code so it will exclude/ignore empty
cells and continue converting the remainder of the column?

Try this...

Dim X As Long
Dim ModifiedDateString As String
For X = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Len(Range("A" & CStr(X)).Value) > 0 Then
ModifiedDateString = Replace(Range("A" & CStr(X)).Value, ":000", " ")
Range("A" & CStr(X)).Value = DateValue(ModifiedDateString) & " " & _
TimeValue(ModifiedDateString)
End If
Next

Rick