Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
try change the load script to
Date(DateColumn,'DD/MM/YYYY') as DateColumn
try change the load script to
Date(DateColumn,'DD/MM/YYYY') as DateColumn
in load script try this
Load *,
Date(columnName,'dd/mm/yyyy') as NewDate
;
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?
Try like this
Date(Date#(Date,'dd/mm/yyyy'),'dd/mm/yyyy')
Or try to upload some sample data
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.
or do in environment variables
SET DateFormat='MM/DD/YYYY';
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...
My environment was set by default to
SET DateFormat='D/MM/YYYY';
so don't think that's the issue...
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...