• Status: Solved
• Priority: Medium
• Security: Public
• Views: 208

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

Hi EE,

This case of data sorting poses an additional complication as compared to Q_27960008. Here I need to sort data according to first column of data. The format here is as follows:

26.01.2012 10:18:43.000

It means:

dd.mm.yyyy hh:mm:ss.000 (after second a decimal point followed by three zeros).

So I need to sort the data on the workbook precisely in a chronological order.

See attached sample file.

Thanks.
Book1.xls
0
capterdi
1 Solution

EngineerCommented:
What's wrong with simply sorting on column A?
0

Commented:
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.
0

Commented:
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))
``````

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

Commented:
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
Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:I")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
``````
0

Author Commented:
Harry,

I prefer VBA.

Sorry.

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

TimeV=Mid(.....
0

Commented:
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.
0

Commented:
capterdi,

I have added a cell content test to minimize the chance of runtime error 13.

``````Sub correctDateandSort()
Dim RwCnt As Long, DTV As Date, DateV As Date, TimeV As Double, i As Long
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
If Not IsDate(Cells(i, 1)) Then
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
End If
Next

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

Author Commented:
Harry,

Good job. Now it works.

Thanks a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.