1

Here is my current input:

14.05.20
12.37.01

And yes, as it shows, there is a line break in the cell...

Here is example sheet: https://docs.google.com/spreadsheets/d/14nLk3nVaeknb9T1JS1EIdH5bqkENuj4OgxF0mPCrzmU/edit#gid=0

How could this be turned into an actual date and time?
I would love it if it could handle the whole column down

  • Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén May 31 at 20:40
2

Edit

In your Google thread you mention:

Would really like to be able to convert this in one formula/step.
...
I would love if it if could handle the whole column down.

In this case, you can use the following formula

=ArrayFormula(IFERROR(  
     (REGEXREPLACE(REGEXEXTRACT(A2:A,"(^\d+\.\d+\.\d+)"),"\.","\/") 
     &" "
     &REGEXREPLACE(REGEXEXTRACT(A2:A,"(\d+\.\d+\.\d+)$"),"\.",":"))
                          *1))

Convert stings in a column to Date time, using an arrayformula


Original answer:

The raw, unformatted value will be returned.
Format the result as Date time from the Format menu.

Convert string to real date time value

Functions used:

| improve this answer | |
  • Hi! I tried messing with REGEX functions to no avail before. Your formula produced the following error for me: "Function MULTIPLY parameter 1 expects number values. But '//////// ::::::::' is a text and cannot be coerced to a number." You can see this on the example sheet here: docs.google.com/spreadsheets/d/… – Drewdavid Jun 1 at 3:22
  • The formula did NOT copy correctly. Please check your sheet again. Formula is fixed. – marikamitsos Jun 1 at 3:35
  • The problem comes down to a displaying bug. Please see the correctly displayed formula on the image. Since you want an array as well, I also updated the formula to include an array. – marikamitsos Jun 1 at 3:41
  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Jun 1 at 4:06
  • Thanks Marikamitsos - I am using this successfully and I find it easier to understand than alternatives. =) – Drewdavid Jun 1 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.