Here is my current input:


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


1 Answer 1



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

     &" "

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:

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

Your Answer

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

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