I have a string of this particular format:
DD/MMM/YY HH:MM AM/PM
e.g.
03/Nov/14 9:00 AM
I could not find figure out how to make Calc recognize it as a date so I had to do the following for a date string in B2:
=DATEVALUE( CONCATENATE( 2000+MID(B2,8,2), "-", IF(MID(B2,4,3)="Jan",1, IF(MID(B2,4,3)="Feb",2, IF(MID(B2,4,3)="Mar",3, IF(MID(B2,4,3)="Apr",4, IF(MID(B2,4,3)="May",5, IF(MID(B2,4,3)="Jun",6, IF(MID(B2,4,3)="Jul",7, IF(MID(B2,4,3)="Aug",8, IF(MID(B2,4,3)="Sep",9, IF(MID(B2,4,3)="Oct",10, IF(MID(B2,4,3)="Nov",11, IF(MID(B2,4,3)="Dec",12,"" )))))))))))), "-", MID(B2,1,2) ) ) + TIMEVALUE( IF(MID(B2,8,1)=" ",RIGHT(B2,7),RIGHT(B2,8)) )
or on one line:
=DATEVALUE(CONCATENATE(2000+MID(B2,8,2),"-",IF(MID(B2,4,3)="Jan",1,IF(MID(B2,4,3)="Feb",2,IF(MID(B2,4,3)="Mar",3,IF(MID(B2,4,3)="Apr",4,IF(MID(B2,4,3)="May",5,IF(MID(B2,4,3)="Jun",6,IF(MID(B2,4,3)="Jul",7,IF(MID(B2,4,3)="Aug",8,IF(MID(B2,4,3)="Sep",9,IF(MID(B2,4,3)="Oct",10,IF(MID(B2,4,3)="Nov",11,IF(MID(B2,4,3)="Dec",12,"")))))))))))),"-",MID(B2,1,2)))+TIMEVALUE(IF(MID(B2,8,1)=" ",RIGHT(B2,7),RIGHT(B2,8)))
and then change the type of the cell containing the formula to Date and chose the desirable format.
All in all a quite involved process. Is there a more automated way?