3

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 '16 at 19:47
3

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.

0

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

=--join("/",index(split(A1,"/"),2),index(split(A1,"/"),1),right(A1,2)) 

JOIN
INDEX
SPLIT
RIGHT

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

=--TEXT(A1,"dd/mm/yy")
=--TEXT(A1,"mm/dd/yy")

Again whether A1 is Text or Date format.

TEXT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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