jquery date column sorting excel output problem

edelsang

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 ..

colin

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

rf1234

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!