1

I am trying to convert date from a column that is a text type, when i try to format directly by Google sheets format option, it only formats a few dates but others not, I have tried using DATEVALUE function but i get the following error: DATEVALUE parameter '26/05/2022' cannot be parsed to date/time.

I have no idea how to proceed with this date cleaning and correctly formatting, any help will very much appreciated.

Sheet example

player0
  • 124,011
  • 12
  • 67
  • 124
Jesus Navarro
  • 57
  • 1
  • 5
  • what is your locale? share a spreadsheet with the date in text. – Mike Steelson May 27 '22 at 17:59
  • I have, there is a link where says "Sheet example" with the data as text – Jesus Navarro May 27 '22 at 18:12
  • we can't decide if in row#16 the date is the 1st of May or the 5th of January! Where and How are they coming rom? – Mike Steelson May 28 '22 at 00:54
  • I spent a good few minutes trying to figure this out myself. When you rich click the cell to change the format (as you would do in excel) there is nothing in the context dialog to change the 'type', only validation. But there is a dropdown you can click that is on the main menu that can change the type from say 'text' to 'date' or 'number' or whatever. Just look around for it and you will find it there. I had the opposite problem where I wanted to run macros/js expressions on a string of a date, not on the date to improve performance. – Steve Tomlin May 30 '22 at 09:50

1 Answers1

1

if you want datevalues try:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124