Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Change Qlikview Excel export Date format to dd/mm/yyy

Hi all,

My SQL database contains dates in dd/mm/yyyy... however, when i export to excel from a qlikview chart, the date format changes to American date format: mm/dd/yyyy


Does anyone know how to tell qlikview to export any dates to dd/mm/yyyy format?


any help would be appreciated!!


Thanks!!

Fab..

1 Solution

Accepted Solutions
techvarun
Specialist II
Specialist II

try change the load script to

Date(DateColumn,'DD/MM/YYYY') as DateColumn

View solution in original post

11 Replies
techvarun
Specialist II
Specialist II

try change the load script to

Date(DateColumn,'DD/MM/YYYY') as DateColumn

prma7799
Master III
Master III

in load script try this

Load *,

Date(columnName,'dd/mm/yyyy') as NewDate

;

Anonymous
Not applicable
Author

Hi all,

The thing is that my dates appear fine (in dd/mm/yyyy format) in my qlikview app, but only convert to mm/dd/yyyy when we export any chart to excel...

So I am assuming this is not a scripting issue, but a setting that has to do with how qlikview exports to excel...

or am I wrong?

prma7799
Master III
Master III

Try like this

Date(Date#(Date,'dd/mm/yyyy'),'dd/mm/yyyy')

Or try to upload some sample data

jonathandienst
Partner - Champion III
Partner - Champion III

That's probably Excel being "helpful". You may need to change the data format in the Control panel localisation option as this is what Excel uses.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Chanty4u
MVP
MVP

or do in environment variables

SET DateFormat='MM/DD/YYYY';

Anonymous
Not applicable
Author

Hi Jonathon,\

Thanks for your response,

are you talking about the windows control panel location settings?

I have double checked the qlikview server and my workstation and all locations are set to English (Australia) correctly as well as Formats for date being correct (dd/mm/yyyy)

Wow I though more people would surely have run into this issue...

Anonymous
Not applicable
Author

My environment was set by default to

SET DateFormat='D/MM/YYYY';

so don't think that's the issue...

Anonymous
Not applicable
Author

Hi all,

just wondering if there any more ideas out there...

My date formats appear fine in my Qlikview tables (see below) - they are just converted to MM/DD/YYYY when I open an export in Excel from a Qlikview chart...

All our workstations on our network are all set to English (Australia) from Control Panel > Location settings and all other displayed dates appear correctly as DD/MM/YYYY format

However I still tried specifying:

DATE("due_date",'DD/MM/YYYY') as ISHBPODueDate

...in my script and what this does, is it changes the date column appear as "custom" format not "text" in excel.


Insidentally, I have Libre office installed on our server and I tried to open the excel export over there... Libre office reads the date format correctly.. so it's definitely something to do with Excel...

Date Formats QV.JPG