Converting Text to DateTime

[#1]

I am receiving an excel file with date and time data on each record (e.g., Apr 21, 2013 10:15 PM).  I have tried to follow a number of the posts on this site on how to convert this to a datetime format within QlikView.  So far no luck.

Can someone please provide me sample code on how to make convert this from text to a single datetime field?

Thanks!

[its_anandrjs]

Use Date and Date# function with Alt functions if required and if you provide any sample data then it will more easy to help.

[ecolomer]

You can convert the format in the excel file to date (YYYY/MM/DD HH:MM) and use in QV the function Date and Date#

[#4]

Thanks, Enrique.  I am getting these excel files automatically, and trying not to introduce any manual steps to ready it for QlikView.  Hoping to simply use code within QlikView to convert it to a datetime. 

[#5]

Anand, attached is a sample Excel file.  I have tried both Date and Date# without success.

[maxgro · Accepted solution]

to interpret the excel field

=date#('Apr 21, 2013 10:15 PM', 'MMM DD, YYYY hh:mm TT')

=date#(excelfield, 'MMM DD, YYYY hh:mm TT')

to interpret and format

=date(date#('Apr 21, 2013 10:15 PM', 'MMM DD, YYYY hh:mm TT'),'DD/MM/YYYY hh:mm:ss')

=date(date#(excelfield, 'MMM DD, YYYY hh:mm TT'),'DD/MM/YYYY hh:mm:ss')

[ecolomer]

OK,

if the format is allways the same you can process them:

Month=Month(left(date_time,3)

Day=Day((mid(date_time, 5,2)

Year=(mid(date_time,9,4) ....

[#8]

Massimo, I can't tell you how many things I have tried but none seemed to work (obviously some typo).  This worked very well.  Thank you!

[#9]

Got the answer.  Thanks!