Excel date format = 30/May/18 vs excel date = 02.01.19 how to convert to date can read ?

[paulyeo11]

Hi All

When my excel date = 02.01.19 My script :- Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [date], it working fine .

But today i get new Excel date format = 30/May/18

I try the script :- Date( Date#([Inv Date], 'DD/MMM/YY'), 'DD/YY/YYY') as [date], Retrun nothing.

Hope some one can help me.

Paul

[Vegar · Accepted solution]

I assume that 'DD/YY/YYY' is a typo in this post and not the desired date format.

Is [Inv Date] recognized  as a date in excel? If so then you might not need to use the date#() at all. Try this instead.

Date([Inv Date],  'DD/MM/YYYY') as [date], 

[paulyeo11]

Hi Sir

Thank you for your sharing. Still cannot. 

Enclosed my excel file.

Paul Yeo

[paulyeo11]

Hi Sir

Your expression is working fine. 

Date([SO DATE], 'DD/MM/YYYY') as [date],

Thank you very much,

Edit :- I forget to mentioned that i make the date readable in excel. ( This way it able to work )

May i ask you , if i don't change manually change the excel  field = [SO DATE], which is hard to maintenance , may i know how to make it work in qlik ?

Paul

Paul

[Vegar · Accepted solution]

My  best guess on why MMM didn't intepret May into a month is that you are using a different language  setup in your system variables. In order to use MMM you need to have a fitted MonthNames definition.

In Portugueese environments the default is 'Jan;Fev;Mar;Abr;Mai;Jun;Jul;Ago;Set;Out;Nov;Dez';

In swedish  the default is 

'jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec

None of these setups will intepret May as the month of May, but an English setup will hence the MonthNames are set to 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec

https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/NumberInt...