Summary

To convert dates in text format dd/mm/yy to a true date in mm/dd/yy format, you can use uses a formula based on the DATE function. In the example shown, the formula in C5 is:

=DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5,2))

Which converts the text value in B5 "29/02/16" into a proper Excel date.

Generic formula

=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))

Explanation 

The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as follows:

1. The year value is extracted with the RIGHT function:

RIGHT(B5,2)+2000

RIGHT gets the right-most 2 characters from the original value. The number 2000 is added to the result to create a valid year. This number goes into DATE as the year argument.

2. The month value is extracted with:

MID(B5,4,2)

MID retrieves characters 4-5. The result goes into DATE as the month argument.

3. The day value is extracted with:

LEFT(B5,2)

LEFT grabs the final 2 characters of the original text value, which goes into DATE as the day argument.

4. The three values extracted above go into DATE like this:

=DATE(2016,"02","29")

Although month and day are supplied as text, the DATE function automatically converts to numbers and returns a valid date.

Note: the year value 2016 was automatically converted to a number when 2000 was added.

Dealing with extra space

If the original text value contains extra leading or trailing space characters, you can add the TRIM function to remove:

=DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),4,2),LEFT(TRIM(A1),2))
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.