Exceljet

Quick, clean, and to the point

Convert text date dd/mm/yy to mm/dd/yy

Excel formula: Convert text date dd/mm/yy to mm/dd/yy
Generic formula 
=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))
Explanation 

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. 

How this formula works

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:

The year value is extracted with with the RIGHT function:

RIGHT(B5,2)+2000

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

The month value is extracted with:

MID(B5,4,2)

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

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.

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))
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.