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:
- month day year
- year month day
- month day (year defaults to current)
- month year (day defaults to 1)
- 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.