Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

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

2 Solutions

Accepted Solutions
Vegar
Partner
Partner

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], 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Vegar
Partner
Partner

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...

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

4 Replies
Vegar
Partner
Partner

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], 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

paulyeo11
Master
Master
Author

Hi Sir

Thank you for your sharing. Still cannot. 

Enclosed my excel file.

Paul Yeo

 

paulyeo11
Master
Master
Author

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
Partner
Partner

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...

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post