Forum Discussion
Date Format in Excel
I have an excel file with roughly 15k records and I have several different date formats showing up in my date column. The information comes from a variety of different sources, and this is why I have several different date formats. While I am working to make sure all dates come to me in one date format from here on out, I need to find a way to change the format on the 15k records which already exist.
Does anyone have a formula I can use to convert all of these different formats into one unified date format?
Here are some examples of some of the various date formats found in my records:
4.19.2018
4/19/18
4/19/2018
4-19-2018
Preferably, I would like them to be in the following format: 04/19/2018
I had the same problem with phone numbers in the record being stored in many different formats and I was able to find a nested formula to strip it down and then reformat the column which worked perfectly. I have tried playing around with several ideas, Substitute statements, and such, but I cannot get the dates all to change with one formula.
Thank you in advance.
Dave
Hi Dave,
To convert dates select the column with them, in ribbon Data->Text to columns, parameters on first two steps of the wizard doesn't matter, on third step select Date with MDY format
As the destination here I selected another column, but you may replace selected one (=$A$1 in this case).
Result is
I use ISO dates format, but you may apply MM/DD/YYYY if it's not default one.
3 Replies
Thank you both for your responses. Greatly appreciated.
Hi Dave,
To convert dates select the column with them, in ribbon Data->Text to columns, parameters on first two steps of the wizard doesn't matter, on third step select Date with MDY format
As the destination here I selected another column, but you may replace selected one (=$A$1 in this case).
Result is
I use ISO dates format, but you may apply MM/DD/YYYY if it's not default one.
Hi,
If you have all 15k records in the format of mm.dd.yy (separator is not a matter).
1. Make sure that your system date format in "mm/dd/yyyy". Try Find and Replace.
Coz only few things can separate the date - Comma (,), Dot (.) , Slash (/), Hyphen (-).
2. You excel column format also should be in "mm/dd/yyyy".
