topic Re: Multiple date formats in QlikView

Multiple date formats

axpresbom — Wed, 01 Dec 2021 12:19:21 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

Or — Wed, 01 Dec 2021 12:32:38 GMT

There's no way to do this, in Qlik or otherwise, because e.g. 10012021 could be either January 10th or October 1st and you have no way of distinguishing which.

Re: Multiple date formats

timothyj — Wed, 01 Dec 2021 19:20:20 GMT

Do you have another column that may tell you where that row of data came from? Like Country? Or country code in a phone number? That might make cleaning it up possible.

Re: Multiple date formats

marco_lapolla — Thu, 02 Dec 2021 11:32:39 GMT

You can only distinguish the dates with day number > 12

Es.
13/12/2014 = EU
12/13/2014 = US

Re: Multiple date formats

Robert9355 — Fri, 03 Dec 2021 10:14:31 GMT

@axpresbom wrote:

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.

Do you have another column that may tell you where that row of data came from? Like Country? Or country code in a phone number? That might make cleaning it up possible.