Sort by date in Open Office
Solved
Tatou
-
tatou28 -
tatou28 -
Hello everyone,
I am trying to sort by date from the oldest to the newest. I select the entire file, I go to: "data" "sort" "column B" "ascending" OK.
I have checked that my entire column is formatted as: date - 31/12/1999
And the result is not what I expect. The sorting is done by day and month, but it does not group the years. What I would like, for example, is:
01/02/1699
01/03/1699
01/02/1700
01/03/1700
whereas I get:
01/02/1699
01/02/1700
01/03/1699
01/03/1700
Thank you very much for helping me with the solution.
Have a nice day everyone
Tatou
Configuration: Windows / Chrome 80.0.3987.163
I am trying to sort by date from the oldest to the newest. I select the entire file, I go to: "data" "sort" "column B" "ascending" OK.
I have checked that my entire column is formatted as: date - 31/12/1999
And the result is not what I expect. The sorting is done by day and month, but it does not group the years. What I would like, for example, is:
01/02/1699
01/03/1699
01/02/1700
01/03/1700
whereas I get:
01/02/1699
01/02/1700
01/03/1699
01/03/1700
Thank you very much for helping me with the solution.
Have a nice day everyone
Tatou
Configuration: Windows / Chrome 80.0.3987.163
7 réponses
Thank you for the information, but I had already tried "replace with" but it didn't work because the character is not visible.
Actually, I just found the solution: the data was corrupted and the troubleshooting turned out to be very simple: select the column, "data", "text to columns", "ok". And there you go, it's fixed.
Another big THANK YOU to everyone for trying to help me. I wish you a very good evening and TAKE GOOD CARE OF YOURSELF!
Best regards
Tatou
Actually, I just found the solution: the data was corrupted and the troubleshooting turned out to be very simple: select the column, "data", "text to columns", "ok". And there you go, it's fixed.
Another big THANK YOU to everyone for trying to help me. I wish you a very good evening and TAKE GOOD CARE OF YOURSELF!
Best regards
Tatou
I do not reproduce.
Are you sure your cells are actually dates?
Erroneous sorting comes from cells in text format.
--
Why hack MS-Office when there is a free solution?
Use OpenOffice
Are you sure your cells are actually dates?
Erroneous sorting comes from cells in text format.
--
Why hack MS-Office when there is a free solution?
Use OpenOffice
Thank you for your quick response.
Yes, I have indeed formatted my entire column as date: 12/31/1999
I have checked a few of them and it's fine, but I can't check them all since I have more than 5000 rows
Do you have a solution?
Thanks in advance
Yes, I have indeed formatted my entire column as date: 12/31/1999
I have checked a few of them and it's fine, but I can't check them all since I have more than 5000 rows
Do you have a solution?
Thanks in advance
Do you think the dates should be split into three columns: day, month, and year?
And if so, do you know a formula to do it automatically?
Thank you
And if so, do you know a formula to do it automatically?
Thank you
Hello,
An example of your file with explanations would be welcome.
To send a file,
Make sure there are NO CONFIDENTIAL DATA
you need to go through an attachment site like cjoint.com
Go to this site: https://www.cjoint.com/
Click on browse,
Find your file,
click on open,
Click on "Create the cjoint link",
Copy the link,
Come back here and paste it in a reply...
Best regards
--
Start by doing what is necessary, then do what is possible, and you will achieve the impossible without realizing it. Saint Francis of Assisi
An example of your file with explanations would be welcome.
To send a file,
Make sure there are NO CONFIDENTIAL DATA
you need to go through an attachment site like cjoint.com
Go to this site: https://www.cjoint.com/
Click on browse,
Find your file,
click on open,
Click on "Create the cjoint link",
Copy the link,
Come back here and paste it in a reply...
Best regards
--
Start by doing what is necessary, then do what is possible, and you will achieve the impossible without realizing it. Saint Francis of Assisi
Hello,
Thank you for your response, but I think I have found the problem. There is an invisible character in front of each date in the table, but it is visible in the text box at the top of the table: the symbol '
I don’t know how it got there, but when I remove it, the date lines up to the right. I suppose it’s this character that is preventing the sorting from working properly. However, it will take me some time to remove all of this from the existing 5,500 lines. With the column search, it doesn’t find the symbol, so I will have to do it manually.
In any case, thank you very much for taking the time to respond to me.
Best regards
Thank you for your response, but I think I have found the problem. There is an invisible character in front of each date in the table, but it is visible in the text box at the top of the table: the symbol '
I don’t know how it got there, but when I remove it, the date lines up to the right. I suppose it’s this character that is preventing the sorting from working properly. However, it will take me some time to remove all of this from the existing 5,500 lines. With the column search, it doesn’t find the symbol, so I will have to do it manually.
In any case, thank you very much for taking the time to respond to me.
Best regards
