2

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

Improve this question
1
  • Please add a brief description of your search/research efforts as is suggested in How to Ask.
    – Rubén ♦
    May 31, 2020 at 20:40

1 Answer 1

Reset to default
3

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
6
  • 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, 2020 at 3:22
  • The formula did NOT copy correctly. Please check your sheet again. Formula is fixed.
    – marikamitsos
    Jun 1, 2020 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, 2020 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, 2020 at 4:06
  • Thanks Marikamitsos - I am using this successfully and I find it easier to understand than alternatives. =)
    – Drewdavid
    Jun 1, 2020 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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