Convert an Incorrect Date Format into a Real Date in Excel

Change an incorrectly formatted/input date into the proper date format for Excel.

This tutorial is specific to when someone inputs a "short" date format incorrectly.

Here are examples of incorrect short date formats:

20/1/2016 Day comes before month so Excel doesn't recognize it. (U.S. format)

1/20/2016 Cell is formatted as text so Excel doesn't recognize it.

1-20-2016 Flat-out wrong date format that Excel can't read.

1.20.2016 Flat-out wrong date format that Excel can't read.

To make these dates readable by Excel, we need to do two things: get each piece of the date separately and input those pieces into a function that turns them into a date.

Sections:

Step 1: Text-to-Columns Method

Step 1: Text Functions Method

Step 2: Input Date Format

Notes

Step 1: Text-to-Columns Method

This is the easiest way for some users to split the dates into separate pieces in order to prepare them to become a date recognized by Excel.

1. Select the date or dates and go to the Data tab and click the button Text to Columns
   
2. In the window that opens, choose Delimited and click Next.
   
3. Check the Other option and input the character that separates your date; in this case it is a /
   
4. Click the Finish button and you should now see your date separated in the worksheet like this:
   

Now, we have the day, month, and year in their own cell and we can easily convert this into a date format that Excel will recognize.

If you have a large list of dates all in the same format, just select that entire list and use Text to Columns on it instead of individually selecting the dates.

Go to Step 2 now if this worked fine for you.

Step 1: Text Functions Method

If you prefer to use an all-formula method for getting the date or you don't have the space in the worksheet to store three extra columns and so you want it to work in a single adjacent column, follow the steps below.

Note: this method is more complex than using the Text to Columns method above.

Get the First part of the Date

We use the LEFT() and FIND() functions.

This method works if the format is 01 or 1 without issue.

=LEFT(A2,FIND("/",A2)-1)

Result:

Get the Second part of the Date

This is a tricky formula that uses the MID(), FIND(), and LEN() functions.

This works with numbers that are in the format 5 and 05 without issue, and that's why it appears to be so complex.

=MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-(LEN(A2)-FIND("/",A2,4)+1))

Result:

Get the Last Part of the Date

This uses the RIGHT(), LEN(), and FIND() functions but is not too complex.

This works regardless of the length/format of the last part of the date.

=RIGHT(A2,LEN(A2)-FIND("/",A2,4))

Result:

Note: these formulas can be a pain, especially the one for the middle value, so just copy them from here to use in your projects.

Change Formulas for Different Separators/Delimiters

It doesn't matter what separates your date, just make sure to tell the formula.

To change the above formulas for different separators, replace everywhere that you see "/" with the correct delimiter.

I did this for you in the example in row 3 and row 4 in the sample workbook.

Here is an example with periods as separators.

But you said you can put it all in one cell!

In the next step, I will show you how to combine it into a single cell.

Step 2: Input Date Format

Now that you are able to separate each piece of the date, it's time to make a real date out of it, and this is the easy part.

We use the DATE() function and simply input the value for the day, month, and year.

The first argument is for the year, the second is for the month, and the third is for the day.

You can see just how easy this is when you have all of the pieces of the date separated into their own cell.

The result of the DATE function is a nice neat date that Excel recognizes as a date:

You can do the same thing with the following examples or you can put them into a single cell.

Put the Formulas into a Single Cell

Using the formula method, we can combine everything into a single cell.

Note: I recommend building the formulas first in their own cells so you can more easily check for errors; then, when it works, combine it into a single cell and copy it down to work for all dates in the list.

Formula for row 2:

=DATE(RIGHT(A2,LEN(A2)-FIND("/",A2,4)),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-(LEN(A2)-FIND("/",A2,4)+1)))

Result:

I left the example for row 3 and 4 unfinished so you can try it out in the sample workbook for this tutorial.

Notes

There is a lot of stuff going on here but, to be honest, you probably just need to copy the formulas in this tutorial and change the separator/delimiter to work with your example and you should be good. If that is too complex or time consuming for you, use the Text to Columns method.

Make sure to download the sample Excel file attached to this tutorial to work with these examples.