topic Re: Export Date format to YYYY-MM-DD in Excel in Reporting Service & Alerting

Export Date format to YYYY-MM-DD in Excel

jakelongnaruto — Fri, 15 Nov 2024 21:26:21 GMT

Hi, is it normal that the date format that is dd/mm/yyyy in the UI becomes 5 digit number after exporting Excel.

For example, when 1/8/2022 after exporting to Excel becomes 44775.

While I can change the format of the column to Date in Excel, it's quite annoying to do this everytime.

Is there a way to fix this in Qlik Sense HUB?

Re: Export Date format to YYYY-MM-DD in Excel

Chirantha — Thu, 10 Aug 2023 14:52:44 GMT

Yes, it is normal for the date format to change when exporting data from Qlik Sense to Excel. In Qlik Sense, your Date field contains a timestamp (e.g. 1/13/20 12:12 AM). In a chart object, you defined the Date dimension as “Date”, so the chart object hides the time and only shows the date (e.g. 1/13/20). When exporting to Excel, all data will be exported including the hidden parts. So, Qlik exports the full timestamp.

If you only want the date to be exported in a specific format, you should use the Floor() function to drop the timestamp and the Date() function to format the date. So, in an object, use the following dimension expression: Date(Floor(DateField), 'YYYY-MM-DD'). This will export the date in the desired format of YYYY-MM-DD.

Please read more in this in this other post

Re: Export Date format to YYYY-MM-DD in Excel

jakelongnaruto — Thu, 10 Aug 2023 14:53:53 GMT

Thank you