Dates standardization in Google sheets using formulas

Question

I have some raw data coming from a system. the issue is that system exports the dates using the US formating to be as M/D/YYYY meanwhile all my sheets using dates as DD/MM/YYYY or D/M/YYYY. i tried using this formula

=text(TO_DATE(DATEVALUE('Row Data'!A2)),"M/D/YYYY")

and then the date changes from e.g. 6/1/2021 to 1/6/2021 (here for June 2021) but after the date is becoming 1/6/2021 i think the other formulas are still reading the date as if it is already the same as 6/1/2021.

Here is a sheet that has some data that I'm struggling with Dates Standardization

So any idea how to fix this mess?

Appreciate your help <3

Aresvik Aresvik · Accepted Answer · 2021-07-06T09:01:51

Based on the formula you've already tried, does this work instead?

=date(regexextract(to_text('Row Data'!A2),".*\/(.*)"),regexreplace(to_text('Row Data'!A2),"\/.*",""),regexextract(regexextract(to_text('Row Data'!A2),"(.*)\/.*"),".*\/(.*)"))

Alternative:

=datevalue(regexreplace(to_text('Row Data'!A2),"(.|..)\/(.|..)\/(.*)","$2\/$1\/$3"))

Assuming you have US dates supplied in any of these formats:

M/D/YY
M/D/YYYY
MM/DD/YY
MM/DD/YYYY

M-D-YY
M-D-YYYY
MM-DD-YY
MM-DD-YYYY

M.D.YY
M.D.YYYY
MM.DD.YY
MM.DD.YYYY

To switch day with month, this will work relative to the example screengrab below:

=arrayformula(if(A1:A<>"", datevalue(regexreplace(to_text(A1:A),"(.|..)[\/\-\.](.|..)[\/\-\.](.*)","$2\/$1\/$3")),))

Dates standardization in Google sheets using formulas

1 Answers