Multiple date formats

[peele87]

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.

[deepanshuSh]

Maybe something like this, 

If (date#(date_column, 'ddmmyyyy')='ddmmyyyy', Date(date#(date_column, 'ddmmyyyy'),

date(date#(date_column,'mmddyyyy'))) as Date_new_column

if you want to separate the two different formated dates into a different fields just use it as separately.

[henrikalmen]

It’s impossible. There’s no way of knowing if 02032023 is ddmmyyyy or mmddyyyy, without having more data (like country). 

[rwunderlich]

As @henrikalmen stated it is impossible given a single date value that could be interpreted either way.  However sometimes we can rely on having dates -- such as month end -- in our data that are unambiguous.  If you know that all dates in a file use the same format you can use the unambiguous date to determine your format. 

What are your date values like? Are they mixed format? If so you are probably out of luck. If a single format per file, can you be sure there will always be a date that is later than day 12?

-Rob