topic Re: Converting Text to DateTime in QlikView

Converting Text to DateTime

Mon, 11 Aug 2014 19:05:37 GMT

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!

Re: Converting Text to DateTime

its_anandrjs — Mon, 11 Aug 2014 19:12:48 GMT

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

Re: Converting Text to DateTime

ecolomer — Mon, 11 Aug 2014 19:19:00 GMT

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#

Re: Converting Text to DateTime

Mon, 11 Aug 2014 21:27:27 GMT

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.

Re: Converting Text to DateTime

Mon, 11 Aug 2014 21:31:24 GMT

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

Re: Converting Text to DateTime

maxgro — Mon, 11 Aug 2014 21:33:42 GMT

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

Re: Converting Text to DateTime

ecolomer — Mon, 11 Aug 2014 21:34:52 GMT

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

Re: Converting Text to DateTime

Mon, 11 Aug 2014 21:42:36 GMT

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!

Re: Converting Text to DateTime

Mon, 11 Aug 2014 21:43:21 GMT

Got the answer. Thanks!