Date with text problem

[pgalvezt]

Hi, I´ve tried to fix this error but I couldn´t. The original file comes with this problem date. Excel read the field like YY/MM/DD but like final text.

Thanks.

[pgalvezt]

Hi,

I could resolve the problem changing the format in:

SET DateFormat='DD-MM-YY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

Finally I left like this:

SET DateFormat='DD/MM/YY';

[Anonymous]

Looks like the data in excel is in format DD/MM/YY.  That means you have to read it this way:

LOAD

date#(Date, 'DD/MM/YY') as Date

FROM C:\Users\pgalvez.AGROSYSTEMS\Desktop\Date.xls (biff, embedded labels, table is Sheet1$);

As for the DateFormat variable, you can keep it as is or change if you prefer.  The date#() function tells how to interpret the data on load in QV.  The variable DateFormat says how to present the data.

Regards,

Michael

[#4]

Try this: Since there are two types of date formats: DD/MM/YY and MM/DD/YY

LOAD Date,alt(Date#(Date,'MM/DD/YY'),Date#(Date,'DD/MM/YY')) as FormattedDate

FROM C:\Users\pgalvez.AGROSYSTEMS\Desktop\Date.xls (biff, embedded labels, table is Sheet1$);

The FormattedDate should be correctly interpreted as date type by Qlikview and should be displayed right-aligned since Qlikview stores dates internally as numeric values.

Thanks,

Shoubhik.