text into date?
using FMP 8.5 on mac os 10.5
i'm importing a set of data in which the date is represented as, for example "23 Jan 2009 05:00 PM WST", that is, in text. Any brilliant ideas on creating a calculation field to convert to a useful date? I've tried:
Date ( Middle ( Show Date; 4; 3); ( Left( Show Date;2)); Middle ( Show Date;8;4 ))
but this doesn't like the month coming in as three character text (and resolves as 23/12/2008). I may have to resort to a sort of lookup to render the month back into two-digit number, but that seems a tad long-winded...
any help appreciated