Date Format in Excel

[dave zwierecki]

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

[#2]

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".

[#3 · Accepted solution]

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.

[#4]

Thank you both for your responses. Greatly appreciated.