Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bobbydave
Contributor III

US date to European Date and still use European dates

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'

1 Solution

Accepted Solutions

Re: US date to European Date and still use European dates

I mean you can try something like this... but I am just concerned that doing this might incorrectly catch some dates as European and some as US... try this

Date(Floor(Alt(Num(P), Date#(P, 'MM/DD/YYYY hh:mm:ss'))), 'DD/MM/YYYY') as P_Date,

TimeStamp(Alt(Num(P), Date#(P, 'MM/DD/YYYY hh:mm:ss'))), 'DD/MM/YYYY hh:mm:ss') as P_TimeStamp,

12 Replies
tharanikannan
New Contributor III

Re: US date to European Date and still use European dates

Hi bobbydave,

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

Re: US date to European Date and still use European dates

Would you show a screenshot for this?

MVP
MVP

Re: US date to European Date and still use European dates

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'))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: US date to European Date and still use European dates

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.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
bobbydave
Contributor III

Re: US date to European Date and still use European dates

Dates appearing

Re: US date to European Date and still use European dates

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

bobbydave
Contributor III

Re: US date to European Date and still use European dates

The ones left indented appear as MM/DD/YYY

The right indented are DD/MM/YYYY

Re: US date to European Date and still use European dates

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?

Partner
Partner

Re: US date to European Date and still use European dates

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