Sorting of data similar to Q_27960008 but with an additional complication.

What's wrong with simply sorting on column A?

If you want a similar solution to Q_27960008..

then you can use this formula:

Supposing your data starts in Cell A1

Put this in new column --- and fill down as necessary
=CONCATENATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2),RIGHT(A1,12))

Then sort on new column, and then Hide or delete the new column.

Saqib Husain, Syed,

The problem is obvious. The date and time in column A is a text string, and the format is DD.MM.YYYY HH:MM:SS

When sorting that columns, it's all being treated as text, and end up sorting using A-Z, 0-9.

Capterdi, do you prefer to use VBA or do you prefer to use formulas?

to use formula, add a column after the date column.

put formula in the new Column B starting B2

=DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+TIME(MID(A2,12,2),MID(A2,15,2),RIGHT(A2,6))

Select allOpen in new window

Then format the column B as MM/DD/YYYY HH:MM:SS and sort by column B.

To use VBA,

Sub correctDateandSort()
RwCnt = Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(2, 1), Cells(RwCnt, 1)).Select
Selection.NumberFormat = "mm/dd/yyyy HH:mm:ss"

For i = 2 To RwCnt
    DateV = DateSerial(Mid(Cells(i, 1), 7, 4), Mid(Cells(i, 1), 4, 2), Left(Cells(i, 1), 2))
    TimeV = Mid(Cells(i, 1), 12, 2) / 24 + Mid(Cells(i, 1), 15, 2) / 24 / 60 + Right(Cells(i, 1), 6) / 24 / 60 / 60
    DTV = DateV + TimeV
    Cells(i, 1) = DTV
Next

Columns("A:I").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:= _
    Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveSheet.Sort
    .SetRange Range("A:I")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A1").Select
End Sub

Select allOpen in new window

Harry,

I prefer VBA.

Sorry.

I run a test. I´m getting a run-time error '13' error on line

TimeV=Mid(.....

capterdi,

I think I know your problem. You are trying to run the macro with already formatted column A.

Run the macro only on the raw data, just like your uploaded file.

Harry,

Good job. Now it works.

Thanks a lot.