Issue with Date Formatting

[Anonymous]

Hello Everyone,

I am having DOB from excel and formatting it like this:

date(Date#(DOB,'DD/MMM/YYYY')) as Test,

After running it I am getting Test as a blank field..any suggestion?

Regards,

Balraj Ahlawat

[tamilarasu]

Could you attach sample file?

[jagan]

Hi,

Try this options

Date(DOB)

If not working load the DOB without any change and check the format in Qlikview how it is read and then use Date(Date#()) to format the date field.

If not working attach sample Excel file, so that it would be easier to identify the issue.

Regards,

Jagan.

[#4]

Hi,

Try this.

date(Date#(DOB,'DDMMYYYY'),'DD/MM/YYYY') as Test

[#5]

remove the slash (/)

[#6]

The other filed which is working fine... What is the format for that field?

[Anonymous]

Thanks everyone, it worked.

[jagan]

Hi Balraj,

But most of the times this excel files are generated automatically from some other 3rd party apps, so changing the format manually is not a good practice.  Because everytime the new file comes you have to manually do this.

Instead you read excel file as it is and then check the format of the date field and using Date(Date#()) convert the format.

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Agreed with you Jagan.

After doing all these settings I came to know that with Date#() function I have to use the excel format of date and then I can use num() & Date() combination.

Instead of doing it manually I am doing it at script level like this:

date(num(date#(DOB, 'Excel Format')),'Required Format') as DOB

[jagan]

Hi,

I think you have to give the Correct Answers to mine instead of your's.

Regards,

Jagan.

[jagan · Accepted solution]

Hi,

There is no need of Num() while converting Dates, Date#() itself gives you the numeric date.  So the script should be

date(date#(DOB, 'Excel Format') ,'Required Format') as DOB

Regards,

Jagan.