Google-sheets – Google sheets MM-DD-YYYY to DD-MM-YYYY

dategoogle sheets

I downloaded CSV data from my Etsy shop to calculate taxes. I'd like to have the date "copied" properly.

The CSV date I get from Etsy is formatted as "02/09/16"

This is "2 September 2016" in Dutch but "September 2 2016" in English.

I cannot seem to find a way to change this to the correct date. When changing language / time and locale settings, Google Sheets always makes sure it's exactly wrong. Setting everything to US or AUS returns the date as a Dutch date, setting everything to Dutch returns an English date!

I was thinking, I could extract the 2 numbers before the first dash "02", which is the month, and use =vlookup() to look up the month from a data sheet and paste this into a correct date. It seems to be a silly workaround but I can't seem to change the date properly.

        Col.A    Col.B
Row. 1)    01    January
Row. 2)    02    February

Lookup 01 and return B1. And so on…

Any ideas? I'm not exactly an Excel / spreadsheets guru.

Best Answer

So you have something formatted mm/dd/yy, but your local format is dd/mm/yy. To recover the dates, you need

  1. Format the imported dates as plain text. Suppose these are in column A
  2. In column B, reconstruct the date in a less ambiguous format using regexreplace, and then use datevalue to turn it into a date. The column B should be formatted as a date.

The formula for B column is 

=datevalue(regexreplace(A2, "(\d+)/(\d+)/\d*(\d{2})", "20$3-$1-$2"))

This replaces either 02/09/16 or 02/09/2016 by 2016-02-09, which is a date conforming to ISO 8601 standard, so it will be understood as 9th of February regardless of the local setting.

(If the date you're starting with is 2/9/16, then the converted form is 2016-2-9 which is not exactly ISO, but datevalue handles it the same way.)

Other separators

If some dates have hyphens - instead of slashes /, you can process them with the following regex:

=datevalue(regexreplace(A2, "(\d+)[-/](\d+)[-/]\d*(\d{2})", "20$3-$1-$2"))

Here, the character group [-/] means one character from those listed.

20th century problems

The above assumes the dates refer to 21st century. More work would be needed to also handle 02/09/98. For example: after doing the above, create one more column with 

=if(year(B2)>2050, date(year(B2)-100, month(B2), day(B2)), B2)

This subtracts 100 years from the date if it's past 2050.

Related Topic