Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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,
Hi bobbydave,
please attach same data. When you define data format , it should display consistently .
Would you show a screenshot for this?
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'))
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.
Dates appearing
So, which ones are European and which one are the US style dates?
The ones left indented appear as MM/DD/YYY
The right indented are DD/MM/YYYY
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?
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