topic Re: Multiple date formats in Qlik Automate

Multiple date formats

peele87 — Sat, 25 Feb 2023 11:25:58 GMT

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.

Re: Multiple date formats

deepanshuSh — Sat, 25 Feb 2023 13:15:58 GMT

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.

Re: Multiple date formats

henrikalmen — Sat, 25 Feb 2023 14:02:10 GMT

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

Re: Multiple date formats

rwunderlich — Sat, 25 Feb 2023 18:03:37 GMT

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