0

I have a Google spreadsheet which I use to track my accounts. I need to add Etsy sales to this, and have added a new sheet which is pasted from the CSV supplied by Etsy.

Unfortunately Etsy only supplies this CSV with dates in US MDY format (e.g. 12/31/2023) but all of the other dates in my spreadsheet are in the more common DMY format (e.g. 31/12/2023). As a result, Sheets does not think that these values are dates (or, in cases where it does think they're dates, it misinterprets them).

How can I get Sheets to recognise these dates and re-format them in a way that makes sense? NB I can't change the locale for the entire spreadsheet, because there are many other colums which contain dates in the correct format.

Alternatively (and slightly less ideally) is there a way I can calculate an intermediate value which Sheets will then recognise as a date? I tried this using a simple Regex (e.g. =REGEXREPLACE(B2, "(\d+)/(\d+)/(\d+)", "$2/$1/$3")) but Sheets still does not recognise the resulting value as a date.

2
  • You may want to try the ISO8601 standard date format yyyy-MM-dd. It works in any locale. Dec 30, 2023 at 21:46
  • That would be ideal, it's what I use for most things, but unfortunately it's not something that Etsy offer - they only give data in US format. Dec 31, 2023 at 1:42

1 Answer 1

0

Use value(), like this:

=to_date(value(regexreplace(B2, "(\d+)/(\d+)/(\d+)", "$2/$1/$3")))

See value() and Working with date and time values in Google Sheets

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.