exporting from qlikview to excel the date format to be mmm-yy

[#1]

Hi,

When I export data to excel the date format is not kept the same as in the Qlikview model. I have my SET DateFormat='MMM-YY';, but when it exports to excel it exports as "dd-mmm" format.

However, if I export for a few months which is less data it exports fine, but once i export for more months which is more data (more records), it exports as "dd-mmm" format !

Anyway around this to be exported in the same format as the model which is "MMM-YY"??

Regards, Jivesh

[stigchel]

Have a look at this thread, last comment maybe a workaround..

Export to Excel issue

[HirisH_V7]

Hey,

Try this for your date field,

Date(Date#(DateField),'MMM-YY')

Wrong date format when export to Excel

HTH,

Hirish

[#4]

Hi Hirish

This is my scrip below, where would I place your recommendation scrip?

I have also did screen shots of the Model, the date format in the Model is correct, but when I export the data to excel, csv, it exports in the date format as dd-mmm. This only happens when I export for many records of data. I have read the "Wrong date format when export to Excel" stream no soluton cn be derived from this one so as the one from stigchel.

FOR EACH vSheet IN '[Secondary FY14]','[Secondary FY15]','[Secondary FY16]','[Secondary FY17]','[Primary FY14]','[Primary FY15]','[Primary FY16]','[Primary FY17]'

Primary_Secondary:

LOAD Secondary_Primary,

     Brand,

     [Vitabiotics Brands],

     Product_SKU,

     [SSD Suppliers_Primary Wholesalers],

     [SSD Customer Name_Primary Ship-to],

     MedPages_Code,

     Year,

     MONTH,

     MAT,

     TEAM,

     [Courier_Non COURIER],

     [RX Territory],

     [Self Care Territory],

     Region,

     [RX RSM Region],

     [Self Care RSM Region],

     Value,

     [SSD Adj Value (SEP)],

     Units

FROM

(ooxml, embedded labels, table is $(vSheet));

[HirisH_V7]

Hi,

check like this using,

Date(Date#(Month,'MMM-YY'),'MMM-YY') as Month

At Month field in your script.

HTH,

-Hirish

[stigchel]

I'm starting to think this is related to your windows regional settings for date, can you check?

[#7]

Hi Hirish

When I use this scrip, my Months disappear?

[#8]

Hi Piet,

My regional settings are set correctly.

[HirisH_V7]

Try exporting only month list-box to excel.

[jonathandienst]

You have two options:

• Export the field as text - this will allow you to control the format, unless Excel recognizes the date format and   converts it to a date, in which case Excel controls the format
• Export the field as a date - Excel will control the format