The other day I downloaded transactions from my bank account and found that the date format had changed from DD/MM/YYYY to MM/DD/YYYY. There is nothing in the CSV file that tells Google Sheets what the format is, so I was seeing 01/09/2019 as "Jan 9, 2019".
I bet TD Bank is not even aware of this, nor the mess this will cause, but anyways, I couldn't find any builtin function in Google Sheets to fix that. Data format did not help either (I guess the formatting says how to render, not how to interpret data).
In the end I resorted to this transformation:
- Import data into Google Sheets
- Notice the dates are all messed up, and swear profusely that the data provider did this, try a few things and fail, search web, nothing really comes up except (hopefully!) this post!
- Insert a column to the right of your date column
- Paste the formula
=REGEXREPLACE(A1, "(\d+)/(\d+)/(\d+)", "$2/$1/$3")and fill the remainder of the new column with that formula - Select the new column and copy it
- Paste the new column over the old, BY VALUE (don't just ctrl-v, instead right-click on the top cell where you want the paste to start, and select Paste... -> Values Only); ignore that the new column now shows re-inverted date format
- Remove the new column you created; the old column now contains dates correctly interpreted.
Note: changing locale might have been an option, didn't try, but for me it wasn't really an option anyways, my current locale is exactly the way I want it.
Top comments (0)