Convert MMM DD, YYYY with timestamp to MM/DD/YYYY in Google Sheets

Question

I have a Google Sheet with dates in this format: Jul 20, 2021, 1:57:53 PM

Unfortunately, Google Sheets doesn't recognize this string to convert the date automatically to MM/DD/YYYY. What formula can I use to quickly convert 2000+ dates to MM/DD/YYYY format?

Here is a sample of some of the data I would like to convert and the type of result I'm looking for: https://docs.google.com/spreadsheets/d/1UcHNOtsMUNOvPcIYVXYRefFfNE6eLXavxFKk6Pp7sus/edit#gid=0

Thank you!

Answer 1

try:

=INDEX(IFNA(TEXT(REGEXEXTRACT(A2:A, "(.+),"), "mm/dd/yyyy")))

or if you need datevalue:

=INDEX(IFNA(VALUE(REGEXEXTRACT(A2:A, "(.+),"))))