Date Conversion Problem

[#1]

Hi All,  I am facing a problem with the date conversion.

I have this excel file in which i have a date column in which i intend to have dates in 'DD/MM/YYYY' format.

But when i import this file into qlikview it appears in 'MM/DD/YYYY'.

Converting these dates using the :

date(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date lends me null values in the frontend.

I also tried using date(makedate(subfield(date,'/',3),subfield(date,'/',2),subfield(date,'/',1)),'DD/MM/YYYY')

but still i am getting null values.  I am not able to fetch dates correctly to the application. I think its a problem of excel file, but i am not able to identify it correctly and solve it.

Any help would be appreciated. 

Thanks in advance. 

P.S. : Attaching the excel file which contains date column.

[#2]

Hi,

you can convert date into number format.

when you show in chart date(date,'DD/MM/YYYY') use like this or

go chart properties->number->date  and change format.

Regards

Vimlesh

[prma7799]

Hi Shashi,

Can you post some small application for the same.

Thanks

PM

[#4]

Hi,

Your Excel Date formate

So that 01 is Month and 05,06,07 are days.

[PrashantSangle]

Hi,

Jonathan is wright about date format.

Your date in xls is in DD/MM/YYYY format.

Therefore you are getting this output.

Check your xls.

Regards

[fashid]

HI,

BUT CANT WE CHANGE THE DATE FORMAT USING THE DATE# FUNCTION IN QLIKVIEW

REGARDS,

NADEEM

[#7]

Hi,

See i need the 01 to be interpreted as date. In excel file i had intended the date to be in DD/MM/YYYY format but instead , the excel file is reading it as MM/DD/YYYY format.

I checked in the excel and i found that 01 is being interpreted as month. What should i do or make changes in the excel.

[#8]

Ya i saw that thank you. But i need the 01 to be interpreted as date. What should i do for that??

[#9]

Hi all,

Thanks for your help, Really appreciate it.

I managed to get things done by making use of makedate().

I have attached what i needed.

[#10]

Hi,

You can't change date in excel by using any format.

If you want to change the date then you should add .

Use this expression in script

makedate(year(date),day(date),month(date) ) as DATE111,

Regards

Vimlesh

[fashid]

Hi,

Could you please attach the formula or the script that you used

would be helpful for learners like me .

Regards,

Nadeem