Date Conversion Issue

[suhasinigm]

Hi Guru's,

I am trying to format the date fields in qlik sense,But not able to achieve ,Please help me out to resolve this .

I have attached the sample excel file.

I tried with following function :

num#([Booking Date]) and date#([Booking Date])

both functions will give different output.

Thanks and Regards

Suhasini G Metri

[Lisa_P]

Your input data has mixed formats.

[suhasinigm]

Hi Lisa,

Yes Input date has mixed format ,how to resolve this .

Thank you for your replay.

[petter]

You can use the Alt()-function to good use to solve different date formats:

like this.

Date( Alt( Date#(Date,'MM/DD/YYYY'), Date#(Date,'DD.MM.YYYY'), Date) ) AS Date

The third parameter to Alt which is bolded is the date in a numerical format as number of days since ~118 years ago.

Alt will try to interpret as date until it gets a valid date through the various formats you select yourself.

[suhasinigm]

Hi Petter,

Thanks for you are replay,

By using

Date( Alt( Date#(Date,'MM/DD/YYYY'), Date#(Date,'DD.MM.YYYY'), Date) ) ,

My both dates should be in 'DD/MM/YYYY' but one date field value is "MM/DD/YYYY'.