Excel 2010 : change text type to date type

Topic

New Reply

I have data that I exported from an online service that has the date field as a text field (11/21/2011 7:49 PM CST) I import this data into a spreadsheet and change data type to date during the import process, but this doesn’t change anything. Once data is imported, I extract the mm/dd/yyyy from the field using the Left function, then copy and paste the values, and then change the field type to date. When I try an sort the sheet by the date it still treats the field as text. Then, I go through and hit F2 and return to edit each field, the field is now read as a date field when I try and sort by that column. That’s not a viable solution when I have over 3000 records to update. Any suggestions.

Reply | Quote

Replies

If you do a Find/Replace, where:
Find = CST
Replace = nothing
all your date & time strings will be converted to date & time values, which you can then sort. The date parts can then be extracted via the INT function (eg =INT(A1)). Alternatively, here’s a macro that will convert any selected range from your import format to simple dates:

Code:

Sub TrimDates()
Dim oCell As Range
For Each oCell In Selection.Cells
  oCell.Value = Format(Replace(oCell.Value, " CST", ""), "mm/dd/yyyy")
Next
End Sub

If we knew which column you needed to process and what else might be in the column that shouldn’t be processed, you mightn’t even need to select anything …

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Outstanding, replacing CST worked perfectly thanks for the help – wasn’t looking forward to editing each record manually.

Reply | Quote

I have another set of data where the date field is in the form of “YYYYMMDD” and it is pure text. How can I convert to an actual date?

Reply | Quote

You’ll need a macro for that one. Try:

Code:

Sub ReformatDates()
Dim oCell As Range
For Each oCell In Selection.Cells
  With oCell
  If .Value Like "########" Then
    .Value = Format(Mid(.Value, 3, 2) & "/" & Right(.Value, 2) & "/" & Left(.Value, 4), "mm/dd/yyyy")
  End If
  End With
Next
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks that worked great, except had to change mid value to “.value,5,2” – thanks again for your help.

Reply | Quote