Add "YYYYMMDD" to text that DateValue() can convert to a date
A lot of systems keep dates in YYYYMMDD format and when extracting text, it would be nice to wrap that in a DateValue() function to convert to date. Fortunately Date.FromText() in PowerQuery/M Language does this, but =DATEVALUE() should too.
As a minimum, it should be upgraded to include a second argument: a date formatting string to tell Excel exactly how the input string is formed.
At present, it seems to use the computer's Windows setting for date to interpret the input string. So their own example of Feb 23, 2011 works for me if entered as "2011/02/23" but does not if entered as "2011/23/02".
That is ridiculous, if one thinks about it. A spreadsheet not only could be USED by a few dozen variations on that theme, presumably breaking it when the users' formats don't match, but also because its input data could be SOURCED from around the world.
This forces one to have to clean data that might already be perfectly clean and well-formed. Wonderfully consistent in its creation. But useless until worked on either by hand, formula, or macro.
For its display, it seems like the cell's formatting would be good, but one could argue that some.
But any way you look at it, it HAS to be upgraded.
Going off-subject a small bit, but related:
There's another Suggestion out there, "cousin" related, that I'm told (by its author) I misunderstood but I like my thought better. That would be that Excel would present every language version of Excel date formatting using language appropriate characters (if Sri Lanka uses "j" for month, then instead of "mmm" to show, say, "Feb", they'd format using "jjj"). HOWEVER, Excel itself would represent the formatting internally in one single way no matter the language version (perhaps "mmm" in this case, perhaps something else). Send the spreadsheet to a customer or co-worker in Mexico and using a Spanish language version and when he opens it, those formatting characters would display to him appropriately for Spanish (maybe "mmm") and any edits he made would have the same thing happen and when you then opened it afterwards in your Sri Lanka language version, you'd still see characters appropriate to your language.
Pieter Kuppens commented
Actually, I like Roy's suggestion better.
I had to import date values from a Dutch Excel sheet into an English Excel sheet, and I ran into a lot of problems. The function Roy suggests would help me fix this easily.
Apart from that the cell should really be a datetime data type and the locale just formatting.
A similar feature should be implemented for currency. Which is just a decimal or double/float type with a locale for formatting
Or simply add a format value to the formula formation: =DATEVALUE("20170802","YYYYMMDD") or for someone else perhaps it would be: =DATEVALUE("8/2/2017","MM/DD/YYYY"). One would obviously use only date specific codes in the second parameter, but those would easily tell Excel how to parse the first parameter's data. And it could easily allow reference to cells where the date data is or formulas that resolve to a date format (so one could us IF() to deal with a variety of formats in the date data (not all input data is clean) or to add the year to a month and day source. One might even create a second parameter that forces a mask: =DATEVALUE("20170802","MM/15/YYYY").
Ed Hansberry commented
that requires doing stuff every time data is brought in or pasted. A formula, once set up, works every time. I can do it with a formula, but it is a long formula using the DATE, YEAR, MONTH, DAY, TEXT, MID, LEFT, and RIGHT functions. Not quick to set up, and I suspect a lot more overhead than if it was just the DATEVALUE() function.
While the method you mention *is* very handy for one time conversions, it does assume the date is in its own cell. That is often not the case if it is copied/imported.
I just found it really odd that Excel supports it in the Get and Transform M Language functions, but not in Excel worksheet functions.
Personally, I select the cells in YYYYMMDD format, then use step 3 of text to columns and tell it that the data is in DATE format and pick YMD as the format. quick and easy.