Need Help Converting Date & Time Stored as Text
 

On Jan 15, 12:55*am, carlo wrote:
On Jan 15, 10:51*am, Donnie wrote:





On Jan 14, 8:36*pm, "Rick Rothstein \(MVP - VB\)"

wrote:
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" wrote in message

....

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- Hide quoted text -

- Show quoted text -

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- 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

Need Help Converting Date & Time Stored as Text
 

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