Formatting Dates from outside software system

[mjohnston0209]

To anyone who can help,

Our software system exported data to excel. However, the date formats cannot be sorted properly. I noticed that if I double click on a cell, so my cursor is showing and then press escape, the format automatically changes to a date format that works for sorting. I have been trying to figure out how to do this as one massive change, but have been unsuccessful. Paste Special doesn't work. I have used left formulas into a column I inserted next to the data and even tried macros. For macros, I don't know how to input my cursor into a cell without the coding listing what the value actually is (there are thousands of unique values). Can anyone help?

Bad Format 8/22/2019 12:00:00 AM
Good Format 8/22/2019

 

[william_man]

Assuming the dates are in a consistent format:

=TEXT(LEFT(A1,FIND(" ",A1)),"m/d/yyyy")

...where A1 is your bad format date.

 

[Joe4]

=TEXT(LEFT(A1,FIND(" ",A1)),"m/d/yyyy")

That does not lend itself to sorting dates in the correct format. The TEXT function will return a text value, not a date value.
And if you if choose that format, it won't sort the way you need dates to, (i.e. 1/1/2019 would come before 7/30/2016).
If you wanted to sort dates formatted as text, you would need to use a format that list years first, then months, then days, and accounts for leading zeroes, so a format like "yyyy/mm/dd").

However, no formulas should be necessary. I think they can convert all the values to valid date entries in a single step using "Text to Columns".
Simply:
1. Select the column
2. Go to the Data menu and select "Text to Columns"
3. Click next until you get to Step 3
4. Choose the Date option, making sure it is using the "MDY" format
5. Click finish

That should convert all those dates to a valid date entry.
You can then set any desired date formatting that you want on the cell.

 

[mjohnston0209]

Thank you both for your input! I wish I would have seen the text to columns answer earlier. I used the date function after I separated out the original date format using left, right, mid, and len formulas. Much harder.