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

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:

## 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.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.