Help with date

[qvqfqlik]

I need to read date values from excel file which is in format of Jan 31 2017  7:00:41:000AM

I am unable to change the date format in excel, but how to read and change the date format in Qlikview ?

Please suggest all possible ways of reading and changing the format of this in Qlikview

Read just the date . timestamp is not necessary.

[sunny_talwar]

My first response worked (with two spaces between Year and Time....

Date(Floor(TimeStamp#(DateField, 'MMM DD YYYY  h:mm:ss:fffTT'))) as Date

[qvqfqlik]

Thanks Sunny! But for few dates it seems not converting.

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD Date as Old_Date,

  Date(Floor(TimeStamp#(Replace(Date, '  ', ' '), 'MMM D YYYY h:mm:ss:fffTT'))) as Date

FROM

[..\..\Downloads\Dates.xlsx]

(ooxml, embedded labels, table is Sheet1);

[qvqfqlik]

Thank you very much Sunny. It worked awesome