topic Re: Multiple date formats in QlikView

Multiple date formats

prantsaga — Mon, 26 Jan 2026 16:26: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

MayilVahanan — Mon, 17 Apr 2023 06:42:23 GMT

Its little bit tricky. Because of 6th of Jan, if its European date, it looks like 06012023, however in US, its looks like 01062023.

How you inform qlik, its 6th of Jan? Do you have any column indicates Europe date or US date?

Re: Multiple date formats

BrunPierre — Mon, 17 Apr 2023 07:10:58 GMT

If I understand the last part correctly, the Alt() function can set multiple possible date formats if you have mixed date formats in a field.

Perhaps,

Date(Alt(Date#(Datefield,'ddmmyyyy'),
Date#(Datefield,'mmddyyyy')),'DD/MM/YYYY') as"new field" ;

Re: Multiple date formats

Jill_Masinde — Mon, 24 Apr 2023 12:45:28 GMT

@MayilVahanan

QlikView date and time functions are used to transform and convert date and time values. All functions can be used in both the load script and in chart expressions. See below examples

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/DateAndTimeFunctions/date-time-functions.htm

https://community.qlik.com/t5/QlikView-App-Dev/date-date-function/td-p/776250

Re: Multiple date formats

MayilVahanan — Tue, 25 Apr 2023 04:04:20 GMT

Hi @Jill_Masinde

Yes, we can convert the number to date, or date / text to different date format in Qlik..

Am asking about data, not about the Qlik functionality.
If the column has both Europe date format (DDMMYYYY - 01062023) and US date format (MMDDYYYYY - 06012023) then if we try to convert into one date format (DDMMYYYY), based on which criteria, we know that whether its 1st of Jun or 6th of Jan?