Changing Date format in Google Sheets-CodePudding

I have copy pasted data from an Excel file into Google Sheet. One of the columns has date formatted as - 11-07-2022.

Google Sheets is reading it as 11th July, 2022 - whereas I want it to read as 7th Nov, 2022. While I can just change the format manually in one of the cells, unfortunately I have more than 1000 rows with such date format.

Is there a better way to do it? I searched online and no matter how I changed the format, it only changed the way the date is displayed rather than reading the date as MM-DD-YYYY.

Please help!

CodePudding user response：

see:

try:

=ARRAYFORMULA(IFERROR(REGEXREPLACE(TO_TEXT(A2:A), 
 "(\d )/(\d )/(\d{2,4})", "$2/$1/$3")*1, A2:A)*1)

CodePudding user response：

IF you know a coding language, you can write a quick script to take the current dates and re-order the parts to put them into the MM-DD-YYYY order.