Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2010 : change text type to date type

    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.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Outstanding, replacing CST worked perfectly thanks for the help - wasn't looking forward to editing each record manually.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks that worked great, except had to change mid value to ".value,5,2" - thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •