Excel won't recognize timestamp in format of "m/d/yyyy h:mm:ss AM/PM"

Question

I have a column of timestamps (strings) formatted like so:

This can be deduced to the following format: m/d/yyyy h:mm:ss AM/PM

I'm trying to get them to be formatted into dates but Excel refuses for some reason. I've tried:

1. DATE()
2. DATEVALUE()
3. * 1. / 1, etc.

All to no avail. I'm surprised Excel can't pick this up.

Answer 1

Try

=DATEVALUE(LEFT(A1,FIND(" ",A1)-1))

LEFT(A1,FIND(" ",A1)-1) extracts the date (1-14-2019)
If your usual date format is like that, wrapping the previous result in DATEVALUE should work.

---

If the previous doesn't work, the following must do it:

=DATE(
    MID(A1,FIND("-",A1,4)+1,FIND(" ",A1,1)-FIND("-",A1,4)-1),
    LEFT(A1,FIND("-",A1)-1),
    MID(A1,FIND("-",A1)+1,FIND("-",A1,4)-FIND("-",A1)-1))

Answer 2

When using - the Eacel expects the format to be yyyy-mm-dd with m-d-yy all we need to do is to substitute the - for /:

=--SUBSTITUTE(A1,"-","/")

Then format as desired. It will keep the full date/time.

If Date is the only thing wanted then:

=INT(--SUBSTITUTE(A1,"-","/")) 

And format as desired.