US date to European Date and still use European dates

[bobbydave]

I have an excel sheet that I am loading.

Date(P, 'DD/MM/YYYY') as [First Detected]

It takes in the European date from Row P of an excel sheet.

However, within Row P, some dates are in US format MM/DD/YYYY

Is there a way of keeping all consistent in European format 'DD/MM/YYYY'

[tharanikannan]

Hi bobbydave,

please attach same data. When you define data format , it should display consistently .

[sunny_talwar]

Would you show a screenshot for this?

[jonathandienst]

Its not really possible to differentiate between DD/MM/YYYY and MM/DD/YYYY in the source data, except for dates after the 13th of the month. Have you got some other field that will help determine the date format to use.

To convert the dates use loginc like this:

     Date(Date#(yourfield, 'MM/DD/YYYY'), 'DD/MM/YYYY'))

[Anil_Babu_Samineni]

We can maintain constant Date field using https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ConditionalFun...

This can identify where ever that format it holds we can change into European. Make sure, How many formats are there that we have include into the picture.

[bobbydave]

Dates appearing

[sunny_talwar]

So, which ones are European and which one are the US style dates?

[bobbydave]

The ones left indented appear as MM/DD/YYY

The right indented are DD/MM/YYYY

[sunny_talwar]

But how do you know that 02/12/2018 is read as 2nd December 2018 and not 12th Feb 2018? May be they all in US format?

[Mark_Little]

Hi David,

Looks like the dates are different lengths.

So you can probably use something like

If(LEN(P)=16,

     DATE(P,'DD/MM/YYYY'),

     DATE(DATE#(P,'MM/DD/YYYY'),'DD/MM/YYYY')

) as P

Mark