Sort by Date column

[vpager]

Hi

I have a worksheet which includes a column of dates as strings in the format of yyyymmdd.

My vba prog:
1. set the column to "Text":
Columns(c).NumberFormat = "@"
2. change each date to "dd/mm/yyyy" format
3. set the column to date format:
Columns(c).NumberFormat = "dd/mm/yyyy;@"
I have also tried "dd/mm/yyyy"

At this point if I use Excel to Sort Ascending by this column, the data is correctly sorted in date order.

But this following vba sorts the data as if it were text:
myWorksheet.Range("A1").CurrentRegion.Sort Key1:=Cells(2, 7), _ Order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom

Any idea why? and how I would get it to sort correctly?

Thanks
Mike

[Bob Phillips]

Why go to all of that kerfuffle? Why not just sort the text dates, that should work fine.

[vpager]

Why go to all of that kerfuffle? Why not just sort the text dates, that should work fine.

Thanks for your reply.
I agree with what you say as a workaround, but I need to use the dates for ongoing analysis, and re-sort the data and back again. So I need the flexibility I get from formatting them this way.

I therefore still need to know why it's not sorting correctly.

Mike

[Bob Phillips]

Can you post the workbook with your code then?

[vpager]

vba code in attached example finishes with a Sort statement. this sorts the data by the Order Date column but treats the values as text instead of dates.
But using Excels sort facility treats the dates as dates.

Any suggestions why the code doesn't do what I need it to do?

Thanks
Mike

[Bob Phillips]

What are you supposed to be sorting on, Order Date or Status Date?

[vpager]

What are you supposed to be sorting on, Order Date or Status Date?

The example is sorting on column 5 being the Order Date.
But same thing happens sorting on either column.

[Bob Phillips]

This reminds me of a problem I once had sorting in VBA, I ended up sorting it twice. I have done that here, and made a few other changes as well, partly to make it work, partly to speed it up.

Try this

[vba]

Option Explicit

Sub FormatMyData()
Dim DataSheet As Worksheet, FileStatusFormatted As String, LineNo As String
Dim c As Integer, FileCheck As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet
With DataSheet

' Order Date column.
Call AdjustDateColumn(DataSheet, 5)
.Columns(5).ColumnWidth = 10.71

' Status Date column.
Call AdjustDateColumn(DataSheet, 7)

' Sort the data by Order Date.
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("E1"), Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
.Range("A1").CurrentRegion.Sort _
Key1:=.Range("E1"), Order1:=xlAscending, _
Header:=xlYes, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Private Function AdjustDateColumn(ByRef sh As Worksheet, ByVal c As Long)
Dim r As Long, sDate As String

With sh

.Columns(c).NumberFormat = "@"
r = 2
sDate = .Cells(r, c)
Do Until sDate = ""

If sDate = 0 Then

.Cells(r, c) = ""
Else

.Cells(r, c) = Right(sDate, 2) & "/" & _
Mid(sDate, 5, 2) & "/" & _
Left(sDate, 4)
End If

r = r + 1
sDate = .Cells(r, c)
Loop

.Columns(c).NumberFormat = "dd/mm/yyyy;@"

.Columns(c).TextToColumns _
Destination:=.Cells(1, c), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 4), _
TrailingMinusNumbers:=True
End With
End Function[/vba]

[vpager]

Ok, can't say I understand what the TextToColumns is actually doing, but it works.
I will read up on it so I know for the future. Does seem a stange thing to have to do though.

Thanks very much for your assistance.

Mike

[Bob Phillips]

Basically, what it is doing is to force the new data format. Simply changing the format still leaves the cell as text, and in Excel you would F2 to force it - TTTC does the same thing.

[vpager]

Would cDate have done the job?

[Bob Phillips]

Not sure, maybe, try it. But the advantage of TTC is that it is a built-in function that works in block mode, so is more efficient.

[vpager]

Thanks xld
cDate wasn't any good so reverted to TextToColumns .

Much aprpeciated.