OpenOffice Calc - turning strings to real Date and Time

Image via Wikipedia

In Open Office Calc, how do you turn the string

'12/3/2009 11:06:35 PM

into the time value:

12/03/09 11:06:35 PM

internally represented by the float:

40150.962905092600

Here is the OpenOffice Calc formula:

=DATEVALUE(SUBSTITUTE(A2; "'"; ""))+TIMEVALUE(SUBSTITUTE(A2; "'"; ""))

coming from Excel, I get thrown for a loop by the semicolons.

Then use the Time/Date format of:

MM/DD/YY HH:MM:SS AM/PM

Image via Wikipedia

This comes up enough so I would appreciate a place to cut-and-paste from. Now I have one! OpenOffice rocks!

[Edit]

Here is another example - starting with the original string containing the date

2008-03-24-00.00.00.000000

This is the formula to turn it into a proper OpenOffice Calc date:

=DATEVALUE(MID(a2;1;4)&"-"&MID(a2;6;2)&"-"&MID(a2;9;2))

Here is the format string:

MM/DD/YYYY

To get the final result:

03/24/2008

Probably the next one I post will have funky time data too... stay tuned...