Google Sheets – Change String Encoding to Date

google sheetsgoogle-sheets-dates

I have some cells in the format 15/05/15. Here is an example. These are dates, but as I copied them into Google Spreadsheets, they are apparently treated as strings. How do I convert them into date objects, preferably in the format 2015-05-15?

Clearly I should be choosing Format->Number and then a date format, choosing MM/dd/YY if needed to pin it down. I might then re-select and choose another format like the YY-MM-dd that I need. But this does nothing.

(Actually, in some cases I got this sort of thing to work and in some cases I did not. I am not sure what makes the difference. Maybe ambiguous strings?)

How do I convert the dates?

Best Answer

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.)

Related Topic