Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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],
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
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],
Hi Sir
Thank you for your sharing. Still cannot.
Enclosed my excel file.
Paul Yeo
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
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