The better (more proper) task is then not to convert the data, but to import it correctly.
When you paste the data, you might want to make sure to paste it as plain text.
Then (if it’s multi-line data) you will get a “Text Import” dialog, that allows you to define import settings, among them the date format of the pasted data. Say, you are pasting the next set:
You know that this data is in MDY format; but the program has no way of knowing that. Even analyzing the first 12 rows, it can’t decide. So it’s the task of user to inform the program about the date format - by right-clicking the respective column:
Another way of letting the program know what settings to use on import is setting the “Language” in the same dialog (the control name is wrong actually - it is locale, i.e. the set of data formatting rules to use when analyzing the data; tdf#138748). E.g., in your case, you know that you are importing the data that uses en-US conventions, so just select that “language”.
That way, the program knows how to interpret the imported data - note that this is not how you want it to display. Then it will recognize the dates, and you will get the proper display according to your preferences (and may further adjust the format using
Alternatively, you may convert already imported data -
Text to Columns is for that. But here you need to understand this:
- The initial import with wrong settings could already “recognize” some of the dates incorrectly - specifically those that were ambiguous, having day no greater than 12. Those will need special handling; it’s better to make sure you get proper results on paste, than trying to rectify the mess from incorrect import later.
- (Maybe not related to your case, but worth noting) the
Text to Columns function may distribute the data from the single column (it only operates on a single column!) to several columns (depends on your settings); and then you should remember that the data in the columns to the right may be re-written.