1

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

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!

player0
  • 124,011
  • 12
  • 67
  • 124
Mayank Agarwal
  • 65
  • 7

2 Answers2

2

see: stackoverflow.com/questions/73767719/locale-differences-in-google-sheets-documentation-missing-pages

enter image description here

try:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
0

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.

John Goodman
  • 1,058
  • 12
  • 14
  • I do know javascript a little bit. Can you guide what is the exact script I need to add in Gscript, I can give it a try. – Mayank Agarwal Dec 01 '22 at 05:40
  • 1
    @MayankAgarwal looks like the answer above should do the trick, let me know if it doesn't and you want some help with the javascript route – John Goodman Dec 01 '22 at 20:33