Formatting "date" for Excel

fermevc

After looking at existing info found over the web and in DT forum, I'm stuck at performing custom formats for Excel export.
This question has been asked (and answered) before, but I need a little bit more help, because the proposed solutions don't work form me like it should.
I've created JSFiddle with basic setup https://jsfiddle.net/fermevc/vmfa4L8t/. In this example, I'm using latest DT minified files (DT 1.10.20, Buttons 1.6.0 with Html5 and JSZip).

In my local setup I've tried to edit "datatables.min.js" in order to add custom date excel format, but no matter what I try, I can't get Excel to automaticaly set that custom format and apply it to "date" column.
Excel complains about damaged data and need to do a repair.
I can see in "styles.xml" newly added style, but I need to manualy select it in "format cells" dialog, and values with leading "0" (eg. 05.10.2019) are not sorted properly. I've edited the code as proposed in other posts for leading "0" issue, with no success.

As a reference, I have an working example (from another web app, based on .NET which uses Datatables for Excel export. In this app, and when I unzip Excel file and take a look in "styles.xml", there is only one entry with "numFldID=164" which has "dd.mm.yyyy hh:mm:ss", and in Excel this is automaticaly selected as Custom format and recognized properly for sorting...
I've tried to utilize this logic, but Excel complains about damaged data...and doesn't format the column data properly...

What I would like to happen is:
- Column with "dd.mm.yyyy" - gets inserted and selected in Excel formats automaticaly.
- Column with "hh:mm:ss" - gets detected as "time"

I'm open for suggestions and will provide any data needed.
Thank you in advance!

colin

Hi @fermevc ,

This thread might help, it's discussing the same thing.

Cheers,

Colin