Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

suhasinigm
New Contributor III

Date Conversion Issue

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

4 Replies
axn
Contributor III

Re: Date Conversion Issue

Your input data has mixed formats.

Dates.PNG

suhasinigm
New Contributor III

Re: Date Conversion Issue

Hi Lisa,

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

Thank you for your replay.

MVP
MVP

Re: Date Conversion Issue

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
New Contributor III

Re: Date Conversion Issue

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