Export Date format to YYYY-MM-DD in Excel

[jakelongnaruto]

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?

[Chirantha · Accepted solution]

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

[jakelongnaruto]

Thank you