Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a field that I'm trying to extract a date from.
It's in the format, text February 2012 text.
Using the functions subfield and mid I now have February 2012.
How can I convert this to a date field, e.g. 01/02/2012
Regards
Tony
Message was edited by: QlikQuest Thanks for the response. I'm not at the office but I've created a similiar scenario. Thanks again.
If this in the case. Use this code,
Date(Date#(Trim(SubField(mid(baddate1,7),'/')),'MMM YYYY'),'DD/MM/YYYY') as DateField
View solution in original post
Can u upload the data file by chance??
try with
date(yourdate#('01/' & left(yourdate,3) & '/' & right(yourdate,4),'DD/MMM/YYYY')) as newdate
whoops just noticed I made a mistake.
load date(date#('01/' & left([Bad Date],3) & '/' & right([Bad Date],4),'DD/MMM/YYYY')) as newdate
Juan,
You are a top guy.
I've amended your suggestion to include the mid function to strip out the bad text and it works a treat.
date(date#('01/' & left(mid(baddate,3),3) & '/' & right(mid(baddate,3),4),'DD/MMM/YYYY')) as newdate,
Many Many thanks.
I don't think there is any need to substring the baddate. You should be able to read it directly with Date#() by supplying the mask.
date(Date#(baddate, 'MMM YYYY'), 'DD/MMM/YYYY')
-Rob
http://robwunderlich.com
Hi,
Yes. I agree with Rob.
It is working perfectly without any use of string functions.
Andrew Hudson
Rob, Andrew,
Thanks for your reply.
When I your suggestion, date(Date#(baddate, 'MMM YYYY'), 'DD/MMM/YYYY')
The script executes OK but no data is returned.
If you could offer any suggestions I'd appreciate it.
Thanks
Tony, can you provide a few sample lines?
Rob,
Thanks for the reply.
I've attached a simple spreadsheet with the type of string supplied from the user and
a QV doc applying your suggestion.
However no data is returned when I apply date(date# etc.
I really would like not to have to substring the field as per your suggestion.
