2 Replies Latest reply: May 15, 2017 12:31 PM by Digvijay Singh RSS

    Date Dual Field

    James Green

      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