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.