View original (French)

Sort by date in Open Office

Solved
Tatou -  
 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

7 answers

  1. ns
     
    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
    3
  2. OOoForum Posted messages 3871 Registration date   Status Member Last intervention   965
     
    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
    1
  3. Tatou
     
    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
    0
  4. Tatou
     
    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
    0
  6. JCB40 Posted messages 3060 Registration date   Status Member Last intervention   479
     
    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
    0
  7. Tatou
     
    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
    0
    1. OOoForum Posted messages 3871 Registration date   Status Member Last intervention   965
       
      The symbol ' indicates that it's text format (see my first response).
      It ended up there because you are probably copy/pasting from other software.
      0
      1. tatou28 > OOoForum Posted messages 3871 Registration date   Status Member Last intervention  
         
        Thank you for the information
        Tatou
        0
  8. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     


    No!

    You select the entire column, you press ctrl+H
    and you replace ' with nothing at all

    --

    Retirement is nice!
    Especially in the West Indies...
    Raymond (INSA, AFPA)
    0