extract a date from a free text field

[#1]

Hi experts!

I have an 'Update' field loaded in from a SharePoint site that I can't alter, that is a text update.  The users always start their update with the date, then text explaining why the action is late!  I want to be able to show how long since the last update, so I'm looking for a way to extract the first 10 characters of the text (which include the date) and make that into a date field that I can use.

So for example, the 'Update' field might contain:

12/10/2014 - Projects are proposed.  A scheme paper for the first of these is ready for review

I'd like to create a date field called 'LastUpdated' from the 12/10/2014

Any suggestions?

[ramoncova06]

if you are sure that the date is always at the start you can use date(left(text,10))

[fvelascog72]

Hi,

Try this:

left([Update],10)          as "LastUpdated"

[swuehl]

LOAD

     Date#(Left(Textfield,10),'MM/DD/YYYY') as LastUpdated,

     Textfield,

     ...

FROM ...;

[#5 · Accepted solution]

Hi,

You can use:

LOAD Update,

DATE#(LEFT(Update,10),'MM/DD/YYYY') AS Date

RESIDENT A;

Or you can find attached sample

Hope helps

Regards,
 
David Sugito

Mobile: + 62 878 0888 9871
Phone: + 62 21 569 823 85 / 86
Email: me@davidshuang.com
Site: www.davidshuang.com

[#6]

Thanks David, that was easier than I thought - but then it always is after someone has shown you the way!

Now I've just got to make sure the users keep entering the dates the same!

[swuehl]

Look into the HELP, alt() function and its example on how to parse differently formatted dates in.