What date formats does Google Sheets recognize?

Question

I have googled this and looked across SO, haven't found anything. How can I get a list of the date formats Google Sheets recognizes?

I found this - https://developers.google.com/sheets/api/guides/formats - about how to format dates to the output you want, but I am looking for a list of the date formats that Google automatically recognizes as dates.

Answer 1

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:

1. month day year
2. year month day
3. month day (year defaults to current)
4. month year (day defaults to 1)
5. year month (day defaults to 1)

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:

• 3/2011 is understood as 2011-03-01 (omitted day defaults to 1)
• 3/11 is understood as 2017-03-11 (omitted year defaults to current)

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:

Understood formats may depend on region and language settings.

Conclusion: just use ISO 8601.