How To Convert Oddly Formatted Data Into Dates In Excel

In this tutorial we are going to look at two ways to convert dates that are formatted in odd ways, for example using commas or periods to separate the month, day, and year, to a format that uses the forward slash, which is more traditionally used in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Sometimes we’ll get an Excel file containing dates and they are in a format that isn’t standardly recognized by Excel as a date format. For example, the month, day, and year are separated by periods, commas, or some other delineation.

Here I have dates in column A and I want to convert them to the format in column C:

I am going to show you two different ways to make this conversion.

The first is using an option that Excel introduced with 2013 and that is Flash Fill. With Flash Fill, you give Excel an example of what you are trying to do, then, using the keyboard shortcut Ctrl + E, Excel will fill in the rest. Here is a tutorial explaining this option:

Flash Fill

The other option is using Text To Columns, found in the Data tab of your Ribbon.

Take a few minutes to watch the video to see how you can use either of these options to reformat your dates into usable ones by Excel

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply