Date#() function

[#1]

Guys,

Basically I'm loading data from an excel file and one of the fields is a date field. It mostly has dates in the form 'DD/MM/YYYY' however excel sometimes automatically reduces the field to the form 'D/M/YYYY'.

Hence when i use the function DATE#(String,'DD/MM/YYYY') it doesnt correctly convert dates which are in the 'D/M/YYYY' form

Any ideas?

Faisal

[Miguel_Angel_Baeyens]

Hello Faisal,

That should work just fine. Take a look at this code:

=Date(Date#('3/9/12', 'DD/MM/YYYY'))

Even not using the leading zeroes for day and month and the century, the date returned is correct.

Hope that helps.

Miguel

[#3]

Thanks Miguel,

I've tried it but it doesnt work for some reason.

I have attached a sample xls and qvw file to show you.

Faisal

[Miguel_Angel_Baeyens]

Hi,

Yes, if you use the wizard you will see that there are dates with the format "DD/MM/YYYY" but there are "dates" that excel recognized but QlikView does not with the format "DD-MMM-YYYY" or just numeric "40100". You will see that at a glance when you just open the file and see how some values are right aligned (numeric correct date formatted) and some are left aligned (string date, literal).

You will need to use the Alt() function then with all possible conditions:

LOAD Trader, 
  Date(Alt(Date#([Opening Date], 'DD/MM/YYYY'), [Opening Date], 'Invalid date')) as date
FROM
Date#.xlsx
(ooxml, embedded labels, table is Sheet1);

Hope that helps.

Miguel