Which converts the text value in B5 “29/02/16” into a proper Excel date. 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:
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.
