The relevant command is DATEVALUE
, which converts a string to date object. However,
Understood formats include any date format which is normally autoconverted when entered, without quotation marks, directly into a cell. Understood formats may depend on region and language settings.
Since these are not understood when pasted in, chances are that DateValue would not understand them either. So you'll need to rearrange the string from dd/mm/yy into the universally digestible yyyy-mm-dd first:
=DATEVALUE(20&RIGHT(A3,2)&"-"&MID(A3,4,2)&"-"&LEFT(A3,2))
The inner commands turn, say, 17/05/15
into ISO 8601 2015-05-17
, which DATEVALUE
understands without ambiguity.
Now that you will have a date object, you can pick a formatting for it by usual means, via Format menu.
(Remark: based on your sample, I'm assuming that both month and day always have two digits. If you had other variations like 17/5/15 or 2/3/14, it would be better to use SPLIT(A3,"/")
to divide the string correctly, and then reassemble by concatenation as above.)
Assuming your string is in cell A1, this formula will convert it to a date. You can then format the date however you prefer.
=date(left(A1,4),mid(A1,5,2),right(A1,2))
Or, take the leftmost four characters as the year, the rightmost two characters as the day, and two characters in the middle starting at position 5 as the month, and convert it to a date.
19961210
turns into
12/10/1996
(standard American date format)
From Google Support:
Best Answer
What inputs are understood as a date depends on the locale setting. For example, entering "3.4.2017" in a cell may or may not create a date. See the list of formats by locale.
There are additional variations within each locale. For example, in the U.S. locale, one can use one of the following orders:
These can be separated either by slashes / or by hyphens -. Separators cannot be mixed; 3/4-2014 is not recognized.
Day can be written in two ways: 4 or 04.
Month can be written in four ways: 9, 09, Sep, September. (Case-insensitive)
The first two digits of year can be omitted if format 1 is used. With other formats such omission either fails to parse, or changes the meaning:
So, all in all we have 5 orders times 2 separators times 2 options for day times 4 options for month times 2 options for year, for the total of 160 formats, minus some disallowed combinations pointed above, minus some over-counting (e.g., if the month is omitted, we shouldn't count its leading 0 option). All of this is for just one locale (U.S.), and is subject to change without notice.
The following is the extent of date format documentation provided by Google:
Conclusion: just use ISO 8601.