Reputation: 95

Formulaa Convert Date format DD/MM/YYYY to MM/DD/YYYY in Google Sheets

I'm having an issue with Datevalue function cause it's not reading DD/MM/YYYY date format. It's giving Error DATEVALUE parameter cannot be parsed to date/time.

I tried changing the number format but the date should be reflecting DD/MM/YYYY cause it's from Australia.

Is there a formula that can change this date format?

Please help.

Upvotes: 3

Answers (5)

Jeff Vdovjak

Reputation: 2003

You can use a formula to convert dd/mm/yyyy to mm/dd/yyyy

Much of the world date format is dd/mm/yyyy but US format is mm/dd/yyyy. One problem is that Google will recognize mm/dd/yyyy as a date and dd/mm/yyyy where dd < 13 (and interpret it wrong) but not dd/mm/yyyy where dd > 12 (it will then recognize the field as a text field).

The formula to convert dd/mm/yyyy to mm/dd/yyyy would be as follows (where A2 is the cell with the date in format dd/mm/yyyy):

=TEXT(CONCATENATE(
INDEX(SPLIT(A2, "/"), 1, 2), "/",
INDEX(SPLIT(A2, "/"), 1, 1), "/",
INDEX(SPLIT(A2, "/"), 1, 3)
), "mm/dd/yyyy")

SPLIT(string, deliminator) will create an array from a string in a column/row format (like a spreadsheet).

INDEX(array, row, column) will grab a single value from the array. As we are only working with 1 row of data all the time "row" will be 1 and column will change. In this example, we are grabbing the second number (mm), then the first number (dd) and then the third number (yyyy).

CONCATENATE() puts these values into a single string so we end up with "mm/dd/yyyy".

TEXT(string, format) will format the string.

NOTE: You could remove the TEXT() function. However, using the TEXT function allows you to output the date in any format such as: "dddd mmmm d, yyyy" which would turn into Saturday January 1, 2022.

Upvotes: 4

stallingOne

Reputation: 4006

No need to change the locale.
I had a format of dd/mm/yy and I got it working with this

=ArrayFormula(datevalue("20"&right(F2:F,2)&"-"&mid(F2:F,4,2)&"-"&left(F2:F,2)))

if I have dd/mm/yyyy, I use this

=ArrayFormula(datevalue(right(F2:F,4)&"-"&mid(F2:F,4,2)&"-"&left(F2:F,2)))

where F is the column with the date.

Upvotes: 1

Matthew Hoag

Reputation: 31

If the question is "How to convert DD/MM/YYYY format to MM/DD/YYYY format", then the answer is:

Assuming Format of J2 is DD/MM/YYYY or D/M/YYYY Format of output, K2 is MM/DD/YYYY or M/D/YYYY (depending on input).

K2 = date(right(J2,4),if(mid(J2,3,1) = "/",mid(J2,4,if(mid(J2,5,1) ="/",1,2)),if(mid(J2,2,1) = "/",mid(J2,3,if(mid(J2,5,1) = "/",2,1)))),if(mid(J2,2,1) = "/",left(J2,1),left(J2,2)))

Upvotes: 3