Skip to main content
Announcements
April 9th: The AI Roadmap: 6 Landmarks for AI-ready Data and Analytics: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jakelongnaruto
Contributor II

Export Date format to YYYY-MM-DD in Excel

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?

Labels (1)
1 Solution

Accepted Solutions
Chirantha
Support

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

 

View solution in original post

2 Replies
Chirantha
Support

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
Contributor II
Author

Thank you