I've got dates in text format with times included. "mm/dd/yyyy h:mm:ss AM/PM" I have set up windows to use the superior yyyy/mm/dd HH:mm:ss format. DATEVALUE() will only convert if the text is in my regional format, however. I have an excel function about 500 characters long with a bunch of FIND()s and MID()s in it, but I'd like to retire that if possible.

Question: How do I convert between the text value "9/29/2011 3:04:23 PM" to a date value, when my regional settings have the date format set to 2011-9-29 15:04:23? Can I maybe format the cells specially to allow it to recognize the text is, in fact, a date?