This formula extract the year, month, and day values separately, and uses the DATE function to assemble them into the date October 24, 2000.
When you're working with data from another system, you might run into text values that represent dates, but are not understood as dates in Excel. For example, you might have text values like this:
October 24, 2000
July 1, 20115
April 24, 1998
February 28, 2014
Excel won't recognize these text values as a date, so in order to create a proper date, you'll need parse the text into it's components (year, month, day) and use these to make a date with the DATE function.
How this formula works
The DATE function takes three arguments: year, month, and day. The LEFT function extracts the leftmost 4 characters and supplies this to DATE as the year. The MID function extracts characters 5-6 and supplies this to DATE as the month, and the RIGHT function extracts the rightmost 2 characters and supplies these to DATE as the day. The final result is a proper Excel date that can be formatted any way you like.
In row 8, the (unrecognized) date format is dd.mm.yyyy and the formula in C8 is:
Sometimes, you'll encounter dates in a text format that Excel should recognize. In this case, you might be able to force Excel to convert the text values into dates by adding zero to the value. When you add zero, Excel will try to coerce text values to numbers. Since dates are just numbers, this trick is a great way to convert dates in text format that Excel really should understand.
To convert dates in place by adding zero, try Paste Special:
Add a zero to an unused cell and copy to the clipboard
Select the problematic dates
Paste Special > Values > Add
To convert dates by adding zero in a formula, use:
where A1 contains an unrecognized date.
Text to columns to fix dates
Another way to get Excel to recognize dates is to use the Text to Columns Feature:
Select the column of dates, then try Data > Text to columns > Fixed > finish
If you need to convert dates to text (i.e. date to string conversion) , you can use the TEXT function. The TEXT function can use patterns like "dd/mm/yyyy", "yyyy-mm-dd", etc. to convert a valid date to a text value. See table below for a list of...
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 ))...
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, RIGHT("apple",3) returns "ple".
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.