Converting text string that contains a date and time to a date

I have given up on Healthvault correcting some issues with their site and am now trying to make a spreadsheet that will plot the data as I want. When I export the data to excel, I get text strings for all, including a date/time string as follows: "6/12/2013 11:04:24.000 AM EDT" Does anyone have any suggestion on how I can convert this text string to an actual date/time string so I can plot the data correctly? The month and day text are variable in length (i.e., 1/1/2013, 12/12/2013, etc.) and the hours in the time text are variable in length, as well. Minutes and seconds appear to be fixed at two digits each, and years at 4 digits each.

It has been years since I have done anything but the most basic of formulas, so any input would be welcome and appreciated!

Mike

OK, that worked great. Thanks! Building upon that, to isolate the time, I have used three steps: First using the SUBSTITUTE function to eliminate the " EDT" from the end of the string, then second, the same SUBSTITUTE function to eliminate the ".000" from the time. Finally I modified your formula to use TIMEVALUE and RIGHT to replace DATEVALUE and LEFT, and all works just fine now. I have isolated the date and time values. That may be a long way around to my solution (and still need to incorporate the time with the date somehow to properly plot several readings on the same date), but it works for now and has been a fun exercise. Just wish I had more time to refine it.

Thanks again!

Mike

Wow! Seems to work very well! Now I will need to figure out why it works (my **** nature). Thank you so much for your help!

Mike