Dates in Excel 2007

[#1]

I have a problem with date values when transposing an excel table.

A simple sheet like this is used:

A load of this data works fine in Excel 2007:

LOAD Date, Value
FROM
theFile
(ooxml, embedded labels, table is Sheet1);

Then, if I use a file with values like this instead:

This time I make a transpose to put figures in the right place

LOAD Date, Value
FROM
theFile
(ooxml, embedded labels, table is Sheet1, filters(
Transpose()
));

The result will be:

The date field is now impossible to convert to something that looks like a date value.

So I think that I have made something wrong. But I make a last test saving the excel file into 97-2003 format. Then, the same procedure will work - I will get date values correctly.

It is no big deal to save the data into an older format - but is this a bug or a user error?

[#2 · Accepted solution]

hi

try using below

load date(Date) as Date,Value

from .........

regards

peter

[#3]

Ouch! That was a too narrow solution, apparently. Works fine. Thank you.

Still - it is interesting that there is a variance in behavior using different kinds of versions in Excel.

[kji]

Actually it looks like all you need to do is to apply numberformat date to the field, the value is read correctly but isn't seen automatically as a date for some reason.