Format Date Excel export

[sergio0592]

Hi all,

I have a date field loaded in my .qvw from Oracle date base. This field is in a chart often used for perform Excel export. So in Excel if the date is recognized as date or as a string it's not the same behaviour when you make a filter. Users want Excel date.

Excel date

Excel string

It's ok when i export to Excel but in the chart i have a DD/MM/YYYY HH:MI:SS  format. And if use date(Field,'DD/MM/YYYY') for remove 00:00:00 it's no longer recognized as date in Excel.

How to achieve this? Thanks for your help.

[Akshada]

Hello,

As per my understanding your excel date is in string format. 1st change it using date and date# function.
Like
DATE(DATE#(Date,'dd/mm/yyyy hh:mm:ss'),'dd/mm/yyyy')

[sergio0592]

Hi,

So it remove the hh:mm:ss part but always not recognized as date in Excel.

[Brett_Bleess]

Sergio, have a look at the following Design Blog posts, they may shed some further light upon things:

https://community.qlik.com/t5/Qlik-Design-Blog/Get-the-Dates-Right/ba-p/1476178

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

One thing that did change was we updated the BIFF component in the 12.20 track I believe to allow .xlsx file creation on export, there may have been some changes in that Microsoft component that is causing some deltas to prior behavior as well...  Sorry I do not have something better for you.

Regards,
Brett