topic Re: Change text to date in QlikView

Change text to date

Fri, 11 Jan 2013 14:26:37 GMT

Hi all,

Unfortunately my data source has a "date" field that is text with the following format, ie, October 15, 2012.

How do I make this into a date? I think it would be easier if the Month was standard 3 letters across the board so I could do something to the effect of: date(date#('date', 'MMM/DD/YYYY')) as New_Date but it doesn't seem to work.

Any suggestions? Do I need to nest a ton of left, mid, rights, find(","), etc? Thanks so much.

Re: Change text to date

Fri, 11 Jan 2013 14:50:19 GMT

check this:

=makedate(right(olddate,4),match( left(olddate,index(olddate,' ')-1),'January', 'February','March','April','May','June','July','August','September','October','November','December'),right(left(olddate,index(olddate,',')-1),index(olddate,',')-1-index(olddate,' ')-1))

Re: Change text to date

CELAMBARASAN — Fri, 11 Jan 2013 14:59:20 GMT

Hi,

Format is available for the full month name also.

Please check this

Date(Date#(datefield, 'MMMM DD, YYYY'))

Hope it helps

Celambarasan

Re: Change text to date

Fri, 11 Jan 2013 15:22:50 GMT

This worked, thanks