Date Dual Field

[cmsjamesgreen]

Hi all

I am importing a .xlsx file that has a column created with US dates.  As I am in the UK, Excel is interpreting some US dates as DD//MM/YYYY and converting them to a serial number (although this serial number is not actually correct, but this is not the issue), and those it cannot convert to a serial number it is leaving as text in MM/DD/YYYY format.  See attached Excel file.

When I import this file in to QlikView the dual field that is created seems to have the number and text representations the wrong way round ie. when I use Num() I get the text date and when I use Text() I get the serial number.  This is only for the dates that Excel has converted to a serial number, the others only have the text representation.

Has anyone else experienced this?  It is making using the interpretation functions difficult.  I don't want to modify the Excel file because I am not the creator and I am likely to be importing more of them.

Thanks

James

[Anil_Babu_Samineni]

May be this?

LOAD Date(alt(Date#([Invoice Date],'DD-MM-YYYY hh:mm:ss'), Date#([Invoice Date],'MM/DD/YYYY hh:mm:ss TT'))) as [Invoice Date]

FROM

[Sales Data Extract Example.xlsx]

(ooxml, embedded labels, table is Extract);

[Digvijay_Singh]

Try this -

LOAD

  alt(Timestamp(Num([Invoice Date]),'DD/MM/YYYY hh:mm:ss tt'),

  Timestamp(Timestamp#([Invoice Date],'M/D/YYYY hh:mm:ss tt'),'DD/MM/YYYY hh:mm:ss tt'))

FROM

[Sales Data Extract Example.xlsx]

(ooxml, embedded labels, table is Extract);