Multiple date formats

[obalikawa]

Just wondering if there is any script that will help me distinguish European date format ddmmyyyy from US date format mmddyyyy? I have an excel document with a date column that contains the two formats(the column itself is formatted as general). Instead of changing excel, is there qlikview script that would allow me to create a new field containing a single format? Thanks.

[rubenmarin]

Hi, qlik uses one general date format, define in the initial variables of the script (DateFormat)

But if an excel has the same column with value (as an example) 01022022 wich in some cases it means one of february and in others 2 of january, this can't be automatically solved.

If the excel has column where it says something related to format date you can use the Date#() function to specify the format od the date that is being readed, in emaple:

If(Country='US'
  ,Date(Date#(ExcelDateField,'MMDDYYYY'))
  ,Date(Date#(ExcelDateField,'DDMMYYYY'))
) as Date