topic Re: Format Date Excel export in QlikView

Format Date Excel export

sergio0592 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Format Date Excel export

Akshada — Mon, 09 Mar 2020 08:00:01 GMT

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')

Re: Format Date Excel export

sergio0592 — Mon, 09 Mar 2020 14:16:33 GMT

Hi,

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

Re: Format Date Excel export

Brett_Bleess — Thu, 02 Apr 2020 13:23:29 GMT

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