Google-sheets – Can’t format hardkey date data as a date in Google sheets

google sheetsgoogle-sheets-arrayformulagoogle-sheets-datesregex

I've got some date data in a Google drive sheet which is imported from a CSV.

The date data is formatted as either dd/mm/yy or dd.mm.yy as hard key data (eg. not formatted as a date)

I got to format > number > date but it doesn't work.

Any idea how I can format these as dates in Google sheets ?

I've made an example spreadsheet here: https://docs.google.com/spreadsheets/d/1FyU526G55dVwKDryaNS1Dz_U2XZpIa9chDCJDcRxQzg/edit#gid=0

Best Answer

Your spreadsheet is in a locale that currently uses the dd/mm/yyyy data format. The data has apparently been imported while the spreadsheet was set to a locale with another date format, which has caused the data to not get converted but stored as text strings instead.

When your csv data is in a date format that differs from that of the spreadsheet, it will not be detected as dates, or worse, will get detected incorrectly, with days and months getting swapped.

To fix the problem, set File > Spreadsheet settings > Locale to a locale whose date format matches that of the data, then change the locale to your real locale.

Your sample spreadsheet already has data that consists of text strings that look like dates. You can convert them to numeric dates with this formula in cell C3:

=arrayformula( if( istext(A3:A), value( regexreplace(A3:A, "(\d\d?)[^\d](\d\d?)[^\d](\d\d\d?\d?)", "$1-$2-$3") ), iferror(1/0) ) )

Format the result column as Format > Number > Date.

Note that while this formula works with the sample data you show in the spreadsheet, it will not give correct results with data that has been incorrectly converted to dates. It happens to work here because all the dates from row 17 down have a number that is greater than 12 in the first position. This number cannot be a month, and the data has apparently been imported with the US locale which has caused the data to not get converted but stored as text strings instead.

See this answer for an explanation of how date and time values work in spreadsheets.

Related Topic