jquery date column sorting excel output problem

jquery date column sorting excel output problem

edelsangedelsang Posts: 1Questions: 1Answers: 0
edited November 2023 in Free community support

Hello
I am using Jquery Datatable. When I print the data as HTML, the date sorting does not work correctly. we don't use Ajax . using export excel button to create excel format file. date format behaves String.. date format is dd/mm/yyyy ..

Answers

  • colincolin Posts: 15,231Questions: 1Answers: 2,594

    We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

    Colin

  • rf1234rf1234 Posts: 2,893Questions: 86Answers: 414
    edited November 2023

    If you want Excel to recognize a column as a date you would need to format it accordingly when exporting. Usually Excel recognizes the field type automatically if you are using American formatting. You can also use data tables built-in styles or make your own which is really cumbersome ...
    https://datatables.net/reference/button/excelHtml5

    I was just experimenting a little: I used a couple of different formats and only one made Excel recognize the format as a date (PHP syntax for date formats):
    d/m/Y not working (it's what you call dd/mm/yyyy)
    m/d/Y not working (you would call it mm/dd/yyyy)
    M d, Y not working

    Then I tried:
    Y-m-d (you would probably call it yyyy-mm-dd)

    That worked! Usually a date is saved as Y-m-d H:i:s in an SQL-database. Exporting the entire date won't work. You need to cut off H:i:s to make it work.

    This was the result exporting M d, Y (German Excel version): Excel only recognizes a string and does not convert display to German standards:

    And this was the result exporting Y-m-d (German Excel version): Excel recognizes a date and does the conversion to German date conventions:

    Hence: Exporting Y-m-d format makes Excel's auto detection work!

Sign In or Register to comment.