Forum Discussion
dates sorting
I cant sort dates in my table from the oldest to the new. the data identified as a date, but when I choose sort, I don't see option to sort like a date.
8 Replies
Since sorting a column of date entries, especially very recent along with very old dates like 17th or 18th centuries produce unpredictable results, I have decided to study the problem. It turns out that I could only resolve this problem by writing a VBA excel program. This program provides instructions and generates a column based on the following rules and assumptions:
1. It was written for the 365 Office platform and the American form of M/D/Y for dates.
2. It assumes that the divider is “/” and includes a 4-digit year, such as 02/06/1854 or 6/5/2001.
3. It may or may not include a time entry such as 6/5/2001 8:55:12 PM.
4. It provides a column transformed to the form Y/M/D, that lends itself to a predictable sort.
5. Generally, this generated column is found to be useful as a hidden column.
6. When a sort of the visible column is requested, it is the hidden column that effects the sorting.
The program is as follows: (Reply for any questions you may have.)
Sub DateAdjust()
Dim A, B, C, D, E, F, G, H, I, J, K, Sel1, FromTo
FromTo = Selection.Rows.Address(0, 0)
If InStr(FromTo, ",") = 0 Then
A = MsgBox("SELECT CELL IN FIRST ROW OF COLUMN OF DATES TO BE SORTED" & vbCr & vbLf & " AND," & vbCr & vbLf & "WHILE HOLDING CTRL BUTTON," & vbCr & vbLf & _
vbCr & vbLf & "SELECT SECOND CELL IN ANY OR THE SAME ROW OF AN EMPTY COLUMN.", vbYesNo + vbDefaultButton1, "SORT ALL DATES (OLD & NEW)")
If A = vbNo Then Exit Sub
Else
B = Left(FromTo, InStr(FromTo, ",") - 1)
C = Right(FromTo, InStr(FromTo, ",") - 1)
Range(B).Select
F = ActiveCell.Column
F = Split(Cells(1, F).Address, "$")(1)
G = ActiveCell.Row
Range(Selection, Selection.End(xlDown)).Select
Sel1 = Selection.Rows.Address(0, 0)
D = Selection.Rows.Count
Range(C).Select
E = Selection.Address(0, 0)
H = ActiveCell.Column
H = Split(Cells(1, H).Address, "$")(1)
I = ActiveCell.Row
Range(E & ":" & H & I + D).Select
Selection.NumberFormat = "@"
ActiveCell.Select
For J = 1 To D
Range(F & G + (J - 1)).Select
Call Adjust(H & I + (J - 1))
Next J
Range(E & ":" & H & I + D).Select
Selection.HorizontalAlignment = xlCenter
ActiveCell.Select
End If
End Sub
Sub Adjust(X)
Dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q
F = ActiveCell
If Len(F) > 10 Then
F = Date
End If
E = StrReverse(F)
J = InStr(1, E, "/")
K = Right(F, J - 1)
L = year(F)
If J = 3 Then G = L & "/" Else G = Right(F, 4) & "/"
A = InStr(F, "/")
If A < 3 Then B = "0" & Left(F, A - 1) & "/" Else B = Left(F, A - 1) & "/"
C = InStr(A + 1, F, "/")
If C < 5 Then D = "0" & Mid(F, C - 1, 1) Else D = Mid(F, C - 2, 2)
H = Left(F, C)
I = G & B & D
Range(X).Select
ActiveCell = I
End SubSort dates on Excel 365 using VBA.
Since sorting a column of date entries, especially very recent along with very old dates like 17th or 18th centuries produce unpredictable results, I have decided to study the problem. It turns out that I could only resolve this problem by writing a VBA excel program. This program provides instructions and generates a column based on the following rules and assumptions:
- It was written for the 365 Office platform and the American form of M/D/Y for dates.
- It assumes that the divider is “/” and includes a 4-digit year, such as 02/06/1854 or 6/5/2001.
- It may or may not include a time entry such as 6/5/2001 8:55:12 PM.
- It provides a column transformed to the form Y/M/D, that lends itself to a predictable sort.
- Generally, this generated column is found to be useful as a hidden column.
- When a sort of the visible column is requested, it is the hidden column that effects the sorting.
The program is as follows: (Respond with any questions you may have.)
Sub DateAdjust()
Dim A, B, C, D, E, F, G, H, I, J, K, Sel1, FromTo
FromTo = Selection.Rows.Address(0, 0)
If InStr(FromTo, ",") = 0 Then
A = MsgBox("SELECT CELL IN FIRST ROW OF COLUMN OF DATES TO BE SORTED" & vbCr & vbLf & " AND" & vbCr & vbLf & "WHILE HOLDING CTRL BUTTON" & vbCr & vbLf & _
vbCr & vbLf & "SELECT SECOND CELL IN SAME ROW OF AN EMPTY COLUMN", vbYesNo + vbDefaultButton1, "SORT ALL DATES (OLD & NEW)")
If A = vbNo Then Exit Sub
Else
B = Left(FromTo, InStr(FromTo, ",") - 1)
C = Right(FromTo, InStr(FromTo, ",") - 1)
Range(B).Select
Range(Selection, Selection.End(xlDown)).Select
Sel1 = Selection.Rows.Address(0, 0)
D = Selection.Rows.Count
Selection.Copy
Range(C).Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Select
E = Selection.Address(0, 0)
Range(E & ":" & Left(E, 1) & D + 1).Select
Selection.NumberFormat = "@"
For J = 1 To D
Call Adjust
Next J
Range(B).Select
If ActiveCell.Row = 2 Then ActiveCell.Offset(-1, 0).Select
K = ActiveCell.Value
Range(E).Select
ActiveCell.Offset(-1, 0).Select
ActiveCell = "*" & K & "*"
Selection.Font.Bold = True
ActiveCell.HorizontalAlignment = xlCenter
Range(C & ":" & Left(C, 1) & D + 1).Select
Selection.ClearContents
Range(E).Select
End If
End Sub
Sub Adjust()
Dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q
F = ActiveCell.Offset(0, -1)
If Len(F) > 10 Then
F = Date
End If
E = StrReverse(F)
J = InStr(1, E, "/")
K = Right(F, J - 1)
L = year(F)
If J = 3 Then G = L & "/" Else G = Right(F, 4) & "/"
A = InStr(F, "/")
If A < 3 Then B = "0" & Left(F, A - 1) & "/" Else B = Left(F, A - 1) & "/"
C = InStr(A + 1, F, "/")
If C < 5 Then D = "0" & Mid(F, C - 1, 1) Else D = Mid(F, C - 2, 2)
H = Left(F, C)
I = G & B & D
ActiveCell = I
ActiveCell.Offset(1, 0).Range("A1").Activate
End Sub
I have no problem with VBA solutions but the regular date processing functionality of Excel can be used, if one so chooses, by noting that the pattern of weekdays and leap-years repeats after 2000 years. This makes working modulo 730485 (#days in 2000 yrs) and subtracting 2000 yrs where necessary a viable solution.
Since sorting a column of date entries, especially very recent along with very old dates like 17th or 18th centuries produce unpredictable results, I have decided to study the problem. It turns out that I could only resolve this problem by writing a VBA excel program. This program provides instructions and generates a column based on the following rules and assumptions:
- It was written for the 365 Office platform and the American form of M/D/Y for dates.
- It assumes that the divider is “/” and includes a 4-digit year, such as 02/06/1854 or 6/5/2001.
- It may or may not include a time entry such as 6/5/2001 8:55:12 PM.
- It provides a column transformed to the form Y/M/D, that lends itself to a predictable sort.
- Generally, this generated column is found to be useful as a hidden column.
- When a sort of the visible column is requested, it is the hidden column that effects the sorting.
The program is as follows:
Sub DateAdjust()
Dim A, B, C, D, E, F, G, H, I, J, Sel1, FromTo
FromTo = Selection.Rows.Address(0, 0)
If InStr(FromTo, ",") = 0 Then
A = MsgBox("SELECT CELL IN FIRST ROW OF COLUMN OF DATES TO BE SORTED" & vbCr & vbLf & " AND" & vbCr & vbLf & "WHILE HOLDING CTRL BUTTON" & vbCr & vbLf & _
vbCr & vbLf & "SELECT SECOND CELL IN SAME ROW OF AN EMPTY COLUMN", vbYesNo + vbDefaultButton1, "SORT ALL DATES (OLD & NEW)")
If A = vbNo Then Exit Sub
Else
B = Left(FromTo, InStr(FromTo, ",") - 1)
C = Right(FromTo, InStr(FromTo, ",") - 1)
Range(B).Select
Range(Selection, Selection.End(xlDown)).Select
Sel1 = Selection.Rows.Address(0, 0)
D = Selection.Rows.Count
Selection.Copy
Range(C).Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(0, 1).Select
E = Selection.Address(0, 0)
Range(E & ":" & Left(E, 1) & D + 1).Select
Selection.NumberFormat = "@"
For J = 1 To D
Call Adjust
Next J
Range(C & ":" & Left(C, 1) & D + 1).Select
Selection.ClearContents
Range(E).Select
End If
End Sub
Sub Adjust()
Dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q
F = ActiveCell.Offset(0, -1)
If Len(F) > 10 Then
F = Date
End If
E = StrReverse(F)
J = InStr(1, E, "/")
K = Right(F, J - 1)
L = year(F)
If J = 3 Then G = L & "/" Else G = Right(F, 4) & "/"
A = InStr(F, "/")
If A < 3 Then B = "0" & Left(F, A - 1) & "/" Else B = Left(F, A - 1) & "/"
C = InStr(A + 1, F, "/")
If C < 5 Then D = "0" & Mid(F, C - 1, 1) Else D = Mid(F, C - 2, 2)
H = Left(F, C)
I = G & B & D
ActiveCell = I
ActiveCell.Offset(1, 0).Range("A1").Activate
End Sub
How do you know you actually have dates, not their text representations? If such 'date' is in A1 and in any empty cell you try =ISTEXT(A1), is it return TRUE or FALSE?
SergeiBaklan its say true. but its still sorting unlike dates.
thank you!
If your texts are in column, you may convert them to dates by Data->Text to Columns selecting on third step of the wizard Date format and proper DMY (or what do you have) format.
