Convert text date/time to a real date time in excel

Question

My data is extracted from an application and it has a text that looks like a date/time in excel. How do I actually convert "3/24/2016 11:22:07 PM" (in text) to a real date/time conversion? I've tried formatting the cells but it doesn't work.

Answer 1

For a date conversion:

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))

For a time conversion:

=TIMEVALUE(TEXT(A1,"HH:MM:SS"))

For datetime conversion:

=DATEVALUE(TEXT(A1,"MM/DD/YYYY"))+TIMEVALUE(TEXT(A1,"HH:MM:SS"))

Where A1 has the data you wish to convert.

By the way, then you may wish to format the cell to a date/time or whatever.

Hope that helps.