Convert an Incorrect Date Format into a Real Date in Excel

Add to Favorites

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

Step 1: Text Functions Method

Step 2: Input Date Format


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.





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.





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.





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:





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.

Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Delete Duplicate Values in All Versions of Excel
Tutorial: How to delete duplicate values from a data set in all versions of Excel. This includes Ex...
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
Macro: This Excel macro filters data in Excel in order to display the top 10 items from the data ...
Average the Visible Rows in a Filtered List in Excel
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...
Filter Data to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
Macro: This free Excel macro filters a data set to display the bottom 10 percent of the data set ...
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course