Date format issue : change 01/01/17 to 42736

[tracycrown]

Dear all

I always face the same problem of reading Date from Excel 2007, please advise is there a bug in QV ?..

Not sure why QV changes the date in Excel 2007 from 01/01/17 to 42736, can someone explain and help to resolve this issue.

Note:

I have not faced this problem in Excel 2003.

Thank You

Tracy

[antoniotiman]

Hi Tracy,

what You see in Excel is the representation of field, but its value is num like 42736.

Try this

Directory;
Temp:
CrossTable(Date, Price)
LOAD * FROM Test.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
NoConcatenate LOAD Company,Date(Date) as Date,Price ;
LOAD Company,Num#(Date) as Date,Price
Resident Temp;
Drop Table Temp; 

Regards,

Antonio

[robert_mika]

Can not open your file but what you got in Excel is a format not true representation of the value.

Try in QV:

Date(yourfield,'DD/MM/YY') as Date

or

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

[sunny_talwar · Accepted solution]

Might be preferable to do the num to date transformation in a single load

Table:

CrossTable(Date, Price)

LOAD *

FROM [Test (1).xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

FinalTable:

NoConcatenate

LOAD Company,

Date(Num#(Date)) as Date,

Price

Resident Table;

DROP Table Table;

[tracycrown]

Dear Antonio

Can you explain the purpose of line 1 below as I understand that line 2 is correct :

1. LOAD Company,Date(Date) as Date,Price ;

2. LOAD Company,Num#(Date) as Date,Price

Thank You, Tracy

[tracycrown]

Dear Robert

I had tried your recommendation but it does not work.

Thank you

Tracy

[tracycrown]

Dear Sunny

Can you enlighten me the purpose of NoConcatenate ?

Thank you, Tracy

[sunny_talwar]

NoConcatenate is used because Table and FinalTable have the same fields and in order to make sure that they do not auto concatenate into one table, we use noConcatenate