Export datetime fields to excel with format WWW h:mm shows fixed WWW in excel

[PedroC]

Hi everyone,

I'm having an issue exporting a regular table with datetime fields that are defined using format WWW h:mm.

This shows the abbreviation of the weekdays and the hour and minute of an event.

This is shown correctly in Qlik Sense as you can see (sorry, it's spanish so L is monday, M is tuesday and so on):

The issue occurs when the user tries to export this data to excel with format. This is what you get:

It seems that excel does not understand the format WWW so it shows "WWW" as a literal.

The data is correctly exported as datetime and if you go into numeric format in excel you can change the correct format that seems to be "ddd" instead of "WWW":

And now datetime shows correctly:

Does anybody know a workaround for this issue?

Thanks in advance!

[nevopotokcloudinary]

Short reply you can post:

---

Yes — I’ve seen this exact issue where you set the format to WWW h:mm in Qlik Sense (so it shows day-of-week abbreviation and time), but when you export to Excel you get the literal text “WWW” instead of the day names. (Qlik Community)

It turns out Excel doesn’t understand WWW as a day-of-week placeholder — in Excel you need to use something like ddd (for example) to represent the abbreviated weekday. In Qlik Sense you could convert the field to text in the load script or expression (for example Text(DateField, 'DDD h:mm')) so it exports as a formatted string, rather than relying on the Qlik format code that Excel won’t recognise.

Hope that helps.

[anat]

convert your data into text format ,so that it will work as expected when you export into excel.

[Chanty4u]

Try this 

By converting text it will solve your issue

Text(DateTimeField, 'WWW h:mm')

[PedroC]

Thank you both Chanty4u / Nevopotockcloudinary, but this solution pops up a new issue. If you convert date fields to text, sorting of data in  tables won't work correctly. This will treat data as strings instead of dates so thrusday will be always before monday and thats not the expected behavior. 

Thank you anyway!