Sort imported date column

M

Michael

I exported a CSV file from one of my on-line accounts. The file has a list
of names, account numbers, and dates. Although the cells containing dates
are formatted as dates in the Format Cells-Number-Date, the sorting function
does not recognize that and will not sort oldest to newest. If I manually
retype a few of the dates into another column, Excel recognizes those as
being dates with no trouble. Is there something about importing from a CSV
that would cause this behavior?
Thank you.
 
D

Don Guillett

Try this after selecting the range with your dates

Sub fixmynums()
Application.ScreenUpdating = False
'lr = Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
For Each C In Selection 'Range("a1:q" & lr)
If Trim(Len(C)) > 0 And C.HasFormula = False Then
C.NumberFormat = "General"
C.Value = CDbl(C)
End If
Next

Application.ScreenUpdating = True
End Sub
 
B

Bob Umlas, Excel MVP

What does the date look like? If it's something like 20080826, you can select
that column, use Data/text to columns, in step 3 change the column Format to
Date & select YMD, then click Finish
 
B

Bob Umlas, Excel MVP

What does the date look like? If it's something like 20080826, you can select
that column, use Data/text to columns, in step 3 change the column Format to
Date & select YMD, then click Finish
 
M

Michael

Don,

I'm sorry to say that I am not a programmer, so I didn't understand what you
are saying. Consequently, I have no way to implement your suggestion, as I
don't know where to begin. Thank you.

Mike
 
M

Michael

Bob,

When I select a cell in the imported column and choose Properties, it is
formatted as a date. The same holds true for the the cell in which I
manually inserted a date. You would think that a plain CSV wouldn't confuse
Excel, especially when I can "tell" it that the column is a series of dates.

Mike
 
G

Gord Dibben

Just because it is formatted as a date does not mean Excel recognizes it as
a date.

Try Bob's Text to Columns. Should convert to real dates.


Gord Dibben MS Excel MVP
 
M

Michael

I tried that to no avail. However, "Paste Special" worked. I should have
thought about that. Thanks to all.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

date 1
Auto-sort worksheet by date in column A 3
Date Frequency 1
Remove date format? 3
Losing date values upon conversion from EXCEL to CSV 0
Text to date format 0
Unusual Date Format 0
Unusual Date Format 1

Top