I need to make this date YYYY/MMM into DD/MM/YYYY

[Anonymous]

Hi Guys,

I need to make this date YYYY/MMM into DD/MM/YYYY, this is one hell of an excel spreadsheet, I don't know what it's doing to date, a right nightmare spreadsheet.

If date was YYYY/MMM you would assume its 2016/Jan, but it's doing 2016/001..... why do months need 3 digits?

Regards

[sunny_talwar]

This isn't working?

Date(Date#(DateField, 'YYYY/MMM'), 'DD/MM/YYYY') as DateField

Make sure you environmental variables includes this:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

[Anonymous]

Nope that didn't work.

[Anonymous]

Hi

Have a look, detailed explanation of date formats

QlikView Date fields

Hope it helps!!

[sunny_talwar]

Can you share few rows of data from your Excel file?

[razvan_brais]

Hy there , go to object settings , and select number tab. There I select your date field and click Date , and in the right you can modify your date format.

[Anonymous]

Here's the field.

Thanks

[sunny_talwar]

May be try with this:

SET MonthNames='001;002;003;004;005;006;007;008;009;010;011;012';

[sunny_talwar]

Sample qvw attached

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='001;002;003;004;005;006;007;008;009;010;011;012';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

LOAD Date(Date#([YEAR QTR], 'YYYY/MMM'), 'DD/MM/YYYY') as [YEAR QTR]

FROM

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

That has worked, but i'll need to check if it doesn't mess up any of the other dates which I assume it will, is there any other way?