sort data by date

Copper Contributor

I need to sort my data of several columns using the date, when I download the Trading data it is in text form which does not help me. 

I need the date format to start with the year_month_day (1 to 30/31)_time of day for example,

2019-08-30 13:10

I have tried to change to date format but does not see to apply changes and it stays the same.

This is one feature of Excel which severely restricts sorting/filtering on my Data.

 

regards and thanks

19 Replies

@graham leo 

Most likely you "dates" are in fact text and need to be converted to real dates. Then you can sort them. Can you upload an example showing how the dowloaded data looks like?

@Riny_van_Eekelenas I have already said in my previous message, I know that it is downloaded in text form and I have tried to change the format but the changes won't apply or stay.

 

I have attached a image or do you need a excel file example?

 

regards and thanks

 

 
 
 

@graham leo 

A file example would helpful.

@Riny_van_Eekelenas attached.

 

thanks and regards

@Riny_van_EekelenHello Riny, the example I sent to you, I already changed to a "date" format from the original General Text format. It still did not give me what I wanted.

regards and thanks

@graham leo 

Noticed that some of the dates were already real dates. Inserted two columns with converted dates. It seems to be consistent with what you want to see. If not, let me know.

 

Used "Text to columns" and converted the texts to dates as DMY, because that seems to be format your date texts are in. After that I reformated the dates as "yyyy/mm/dd hh:mm".

 

Hello @Riny_van_Eekelen, this looks like a solution, thank you.

 

Did you create the format yourself? 

 

If so can you give a outline as to how you did it?

I think I have an idea but it would be good from you to confirm it.

 

regards Graham

@graham leo 

The date-and-time format is based on what you can find under "Custom" cell format, with a slight modification. If you can't see the particular one that I used, you can just type "yyyy/mm/dd hh:mm" in the field where it says "Type:". The picture below may clarify it. Be it that I'm on a Mac and your screen may look a different in case you are on a PC.

Screenshot 2020-01-16 at 06.42.44.png

 

@Riny_van_Eekelenthank you good outcome,

 

regards graham 

@Riny_van_EekelenI have done everything as you said but still stays as "text". The filter is asking either

A to Z or Z to A not "oldest" or "newest" 

 

regards

@graham leo 

Strange! Took your original file, re-did, text-to-columns as described, and applied the custom format.

Screenshot 2020-01-16 at 08.52.45.png

On my system I can sort oldest to newest when I go via Data / Sort. However, when I sort via the the Filter button, like you describe, it only allows for Ascending or Descending. But the fact that it has "grouped" the data by 2020 and 2019 tells me that Excel is recognising real dates.

Screenshot 2020-01-16 at 08.54.56.png

@Riny_van_Eekelenwell it is telling me I haven't an outcome, yet.

 

I'll try youtube.

@graham leocan other people give me a suggestion, I need a solution now on this issue.

I have been struggling for along time working around this problem but it is shooting my productivity to pieces and Microsoft is not giving me an outcome which I have paid for.

 

Some tutorial may help me.

 

regards

@graham leoI'd pay money to get this fixed up

Yo las convierto a fecha , primero con extraer en 3 columnas y luego concatenar para dar formato fecha finalmente @graham leo 

@Riny_van_Eekelenhello Riny,

Now have an immediate positive outcome.

As you suggested I kept it in "csv" format and changed the settings in the system to UK rather than Australian format (not to sure about that one because it appears to be similar).

 

thanks and regards

 

@graham leoThe idea of saving in a Excel Workbook rather than a csv format was a serious negative outcome for me. The information saying by saving in csv format you will lose features is MIS-information.

I don't know what features they are talking about, all I know is Microsoft cause me a lot of frustration for months on end. What I have now is, my data when downloaded no work/ modification on the dates format is required, I can start on the Analysis of my data immediately. Thanks for nothing Microsoft, maybe I should be entitle for a refund at least, you would not be able to put a figure on the productivity. 

@graham leo 

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 Sub

@Friedemann Schaefer  hello, I didn't think anybody was worrying this, I haven't. Once I could do my work in csv before saving it in Workbook format that was good enough for me because there is continually new information anytime of the day.

You appear to have done a serious amount of work, but I won't being going anywhere near it because I do not have the need and the time/productivity I would lose would be not acceptable.

 

My Excel interest is now Conditional Formatting at my leisure.

 

Thank you for your reply and interest