Convert Text String into Date/Time Value

Question

I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern:

Mon Mar 11 19:05:32 +0000 2019

The pattern always repeat itself:

[day] [month] [date] [HH:mm:ss] [Time Zone offset] [YYYY]

Can this text string pattern be turned into a date/time value using a formula?

Ideally, I'd like to split the date/time values represented in the text string above into two columns: date & time:

date       | time
DD/MM/YYYY | HH:mm:ss

Answer 1

={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")),
  TEXT(INDEX(SPLIT(A1, " "), 1, 4), "hh:mm:ss")}

with timezone in account:

={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")),
  TEXT(INDEX(SPLIT(A1, " "), 1, 4)+IFERROR(
  TIMEVALUE(JOIN(":", SPLIT(REGEXREPLACE(REGEXEXTRACT(A1, "\+(\d+)"),
  "(.{2})", "/$1"),"/"))),
  TIMEVALUE(JOIN(":0", SPLIT(REGEXREPLACE(REGEXEXTRACT(A1, "\+(\d+)"),
  "(.{2})", "/$1"),"/")))), "hh:mm:ss")}

Answer 2

Your string is space-delimited, so we can cut it up using SPLIT, then grab the parts we need by number using INDEX.

The month needs a bit of coercing because it is a word, so I suggest asking the MONTH function for the date of that month's 1^st, but then just the month-number of that month. It is able to convert the string "Mar1" to a date, for example, and then output its month; 3.

Then we can just format the destination cell as a Date and send the appropriate inputs to the DATE function. For example:
=DATE(INDEX(SPLIT(A1," "),1,6),MONTH(INDEX(SPLIT(A1," "),1,2)&1),INDEX(SPLIT(A1," "),1,3))

Along the same lines, you could simply make sure the time's destination cell is formatted as Time extract the time portion of the string:
=INDEX(SPLIT(A1," "),1,4)

At my time of writing your question does not address the time zone offset. Note that any solution may have to alter the output time, and occasionally date, based on the effect of the offset.

Answer 3

The short answer is yes, but I consider more convenient to use a custom function.

Google Sheets handle dates as serialized numbers. The timezone is based on the spreadsheet timezone settings. Unfortunately there isn't a built-in spreadsheet function that converts text as the one shown on the question directly, your formula should extract the parts and apply the time zone offset considering the spreadsheet time zone setting.

Suggested algorithm for a built-in function based solution:

1. Look at the spreadsheet settings to get the time zone setting write the time zone offset to one cell.
2. Remove the time zone offeet from your date, and reorder the rest of the date-time parts to build a DATEVALUE supported date-time as text value.
3. Apply the required timezone offset.
4. Add the resulting value to one cell for the date, and another cell for the time in order to use the cell number formatting options to display the date on first and the time on the second.

If you want to take the timezone setting automatically then you should use Google Apps Script Class Spreadsheet method getSpreadsheetTimeZone() and convert the returned script to a timezone offset value. This results too convoluted considering JavaScript handle date-times considering the timezone offset.