Convert text date time to a real date time in excel

Question

My data was taken via a program, and it had text that appeared to be a date and time in Excel. How can I truly translate the text "3/24/2016 11:22:07 PM" into a date-to-time conversion? Although I tried formatting the cells, nothing happened.

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"))