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'
Solved! Go to Solution.
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,
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.
Looks like the dates are different lengths.
So you can probably use something like
) as P