Convert a string to Date & Time

[MalcolmCICWF]

I was wondering, what would be the easiest way to convert this string into a date and time with the AM/PM on it? I don't know who though it was a good idea to output an '@' in the middle of this. Right now I am doing a 'left' and taking out the date part and converting, but I am having issues removing the time and formatting. There must be an easier way...

[swuehl · Accepted solution]

Just use the timestamp interpretation function Timestamp#() with an appropriate format code:

=Timestamp(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT'))

edit: If you want to separate date and time, just use the timestamp returned:

=Date(Floor(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

=Time(Frac(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

[tamilarasu]

Another solution could be,

=Date(Date#(Replace('2013-07-30 @ 12:44 PM',' @ ',' '),'YYYYY-MM-DD hh:mm TT))

=Time(Date#(Replace('2013-07-30 @ 12:44 PM',' @ ',' '),'YYYYY-MM-DD hh:mm tt'),'hh:mm TT')

You can also use PurgeChar syntax to remove the @ symbol form your string field.