Convert text date dd/mm/yy to mm/dd/yy in Excel
This tutorial shows how to Convert text date dd/mm/yy to mm/dd/yy in Excel using example below.
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:
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 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 retrieves characters 4-5. The result goes into DATE as the month argument.
The day value is extracted with:
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:
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: