topic Incorrect Date format after Export to Excel (becomes negative numbers) in App Development

Incorrect Date format after Export to Excel (becomes negative numbers)

limfungkeat — Mon, 01 Apr 2024 02:45:59 GMT

Dear gurus ...i have been searching and reading all the Solved solutions but all these were not helping

https://community.qlik.com/t5/Qlik-Reporting-Service/Export-Date-format-to-YYYY-MM-DD-in-Excel/td-p/2104574

https://community.qlik.com/t5/App-Development/Wrong-date-format-is-coming-while-exporting-qliksense-report/td-p/1673980

Please refer attached Excel Sheet, all the date columns are correct Except the 'Activities Dates Column'. For your information, the same chart has been done in Qlikview and the column has got no problem after Export to Excel . Wonder why it only happens in Qliksense, and to that specific column only.

Re: Incorrect Date format after Export to Excel (becomes negative numbers)

ryan1969 — Wed, 03 Apr 2024 04:23:33 GMT

@limfungkeat Socrates GM wrote:

Dear gurus ...i have been searching and reading all the Solved solutions but all these were not helping

https://community.qlik.com/t5/Qlik-Reporting-Service/Export-Date-format-to-YYYY-MM-DD-in-Excel/td-p/2104574

https://community.qlik.com/t5/App-Development/Wrong-date-format-is-coming-while-exporting-qliksense-report/td-p/1673980

Please refer attached Excel Sheet, all the date columns are correct Except the 'Activities Dates Column'. For your information, the same chart has been done in Qlikview and the column has got no problem after Export to Excel . Wonder why it only happens in Qliksense, and to that specific column only.

Hello, @limfungkeat

I can see your post, That you’re encountering an issue with date formats when exporting data from QlikSense to Excel. Specifically, the ‘Activities Dates Column’ is not displaying correctly in Excel. Let’s address this:

Date Format Issue:

When exporting data from QlikSense to Excel, the date format may not be preserved as expected.
The issue might be related to how QlikSense handles date fields during export.

Possible Solution:

To ensure that the date format is consistent, consider using the Floor() function in your QlikSense expression for the ‘Activities Dates Column’.
Modify the expression to something like: Date(Floor(ActivitiesDate), 'YYYY-MM-DD').
This will drop the timestamp and export only the date part in the desired format.

Test and Verify:

Apply this change to your QlikSense chart and export the data to Excel again.
Check if the ‘Activities Dates Column’ now displays correctly in Excel.

Remember to adjust the expression according to your specific date field name and desired format. If you encounter any further issues, Please tell me I am happy to help you.

Best Regard,
ryan1969

Re: Incorrect Date format after Export to Excel (becomes negative numbers)

limfungkeat — Mon, 01 Apr 2024 07:01:59 GMT

Thanks Ryan ..but i have mentioned in my post that , i did referred and already done the solution you proposed. But still the data is not exported with the format as required .

Re: Incorrect Date format after Export to Excel (becomes negative numbers)

BrunPierre — Mon, 01 Apr 2024 08:11:56 GMT

I think the problem still lies with formatting. Please provide a raw sample of the field named 'Activities Dates'.