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.
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.
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.
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.
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:
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:
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.
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.
In the next step, I will show you how to combine it into a single cell.
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.
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.
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.