mixed date format in one column

[ZuzJsk007]

Hi everybody, I'm new to Qlik and I stumbled upon an interesting thing:

I have data in xlsx format, various tables, various datatypes, all O.K. loaded into Qlik except one column "Access date". In Excel, it looks like a normal datum. But when I create a table in Qlik, it looks like table below. I tried formating via date() in the Table (see below). I tried formatting also in load (date() and date#())  but with no effect.

EmployeeID	AccessDate	=date([Access date],'d.m.yyyy')	month([Access date])	right Access Date I want to see
1	9.6.2020	-	-	9.6.2020
1	19.6.2020	-	-	19.6.2020
1	29.9.2020	-	-	29.9.2020
1	43995	13.0.2020	June	13.6.2020
1	43998	16.0.2020	June	16.6.2020
1	44003	21.0.2020	June	21.6.2020
1	44009	27.0.2020	June	27.6.2020

Please, any ideas where the problem is?

Thanks a lot!

[lotfiblo]

Hi @ZuzJsk007 ,

Welcome to Qlik Sense.

Try this:

Date([Access date],'d.M.yyyy')

M must be in In capital letters 😁

Best,

Lotfi

[ZuzJsk007]

Thank you vor your suggestion 🙂 Unfortunately, it doesn't help with everything 😕 I still see the column divided to NULLs and dates 😞

EmployeeID	AccessDate	=date([Access date],'d.m.yyyy')	month([Access date])	right Access Date I want to see
1	9.6.2020	-	-	9.6.2020
1	19.6.2020	-	-	19.6.2020
1	29.9.2020	-	-	29.9.2020
1	43995	13.6.2020	June	13.6.2020
1	43998	16.6.2020	June	16.6.2020
1	44003	21.6.2020	June	21.6.2020
1	44009	27.6.2020	June	27.6.2020

[brunobertels]

Hi  

try this 

date(date#([Access date],'D.M.YYYY'))

hope it helps

[arulsettu · Accepted solution]

Try this for different date formats

load * Inline [
date
43995
21.9.2020
];

date(alt(date#(date,'D.M.YYYY'),date(date,'D.M.YYYY')))

[ZuzJsk007]

Thank you, the different formats in date column were exactly the problem!