Search code examples
google-sheetsgoogle-sheets-formuladate-format

Can't apply custom date format

My cell contains a regex formula to extract a date from a string (result is "mm/yyyy", for example "11/2022"). I define a custom date format (so it looks like "November 2022") but Sheets won't apply it.

What could be the reason and/or what am I doing wrong?

edit: a sample of data. My cell contains the following formula

=TO_DATE(REGEXREPLACE(A2, "([0-9]{4})([0-9]{2})(.*)", "$2/$1"))

note: isdate(mycell) returns TRUE

Solution

  • Use DATEVALUE instead:

    =DATEVALUE(REGEXREPLACE(A2, "([0-9]{4})([0-9]{2})(.*)", "$2/$1")

    PS: you can also use it in array formula ;)