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.

  • 4
    This is "2 September 2016" in Dutch but "September 2 2016" in English ... is confusing, I think you meant it's "February 9, 2016" in English.
    – user79865
    Feb 20, 2016 at 19:47

2 Answers 2


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.


To switch order of day and month regardless whether in Text or Date format:



If you know which 'direction' you need then whichever of the following suits would be shorter:


Again whether A1 is Text or Date format.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.