date conversion from DD-MMM to MM/DD/YYYY

[hosasahana]

hi all,

I have an excel source which has date in this format as shown in below picture. After load qlik stores it as 20-Sep.I want to convert this to MM/DD/YYYY ()9/20/2020)format in qlik script. I tried date,date#,makedate etc... but nothing works. pl can someone help?

[zhaofeng]

Could you please provide your excel file.I think it may caused by data format

[hosasahana]

hi,

thanks for responding. I have attached a test file. Please check.

(I want to change this format in click script and not in excel.)

[zhaofeng]

Hi

I've read your attachment.I found the date format is different from your previous screenshot.I also got a screenshot.Whatever,I've made an example in the qvw file,please check it

[hosasahana]

hi,

I am unable to open the qvw locally as I do not have qlik in local machine since we work on cloud and  currently without enterprise version cannot install as well I guess.

Please can you post the code line used directly if possible? sorry for the trouble.

[QFabian]

HI @hosasahana , this works fine for me :

LOAD A,
Month,
date("Month", 'MM/DD/YYYY ') as Date,
[Call Count],
D
FROM
test_dateconversion.xlsx
(ooxml, embedded labels, table is Sheet1);

[QFabian]

that works  fine!

[zhaofeng · Accepted solution]

Hi

I paste the source code below,please check it.

LOAD

          A as User,
          Month,
          date(num(Month),'MM/DD/YY') as Year_Month_Day,
          [Call Count] as Call_Count
FROM  test_dateconversion.xlsx(ooxml, embedded labels, table is Sheet1);