jquery date column sorting excel output problem
jquery date column sorting excel output problem
![edelsang](https://secure.gravatar.com/avatar/d87381b69bd6bd14856fb1c55df40bac/?default=https%3A%2F%2Fvanillicon.com%2Fd87381b69bd6bd14856fb1c55df40bac_200.png&rating=g&size=120)
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
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
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:
![](https://datatables.net/forums/uploads/editor/fv/v1kvdme18eci.png)
And this was the result exporting Y-m-d (German Excel version): Excel recognizes a date and does the conversion to German date conventions:
![](https://datatables.net/forums/uploads/editor/bf/4iw5i82luobm.png)
Hence: Exporting Y-m-d format makes Excel's auto detection work!