6

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
2
  • Please provide your desired output, using that mentioned date for example. Also perhaps an example with a non-zero Time Zone Offset, and its corresponding desired output.
    – Joel Reid
    Mar 12, 2019 at 13:12
  • Ideally, I'd like to split the text string in my example above into two columns: [date] with values like this: DD/MM/YYYY, and [time] with values like this: HH:mm:ss Mar 12, 2019 at 13:32

3 Answers 3

7

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

0

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")}

0

6
  • Your formula assumes that the spreadsheet timezone is UTC +0000, right?
    – Rubén
    Mar 12, 2019 at 14:28
  • @Rubén nope, only what's in the string of A2 as +0130
    – user0
    Mar 12, 2019 at 14:39
  • For some reason i get an error message on the time column saying: "Function REGEXEXTRACT parameter 2 value "+(\d+)" is not a valid regular expression." Mar 13, 2019 at 12:48
  • 1
    WOW!!! works like a charm :-) Is there a way to handle both date and time in one single datevalue variable, this way it could be fetched for time or for date whenever needed? Mar 13, 2019 at 13:14
  • you mean like this? i.stack.imgur.com/D1fI0.png
    – user0
    Mar 13, 2019 at 13:41
1

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 1st, 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.

1
  • Watch those TZ offsets… really! A hideous-but-clear way to tackle them would be to SPLIT out the offset, then further cut that into a [potentially negative—I've seen it] hours/24 and minutes/1440, yielding the portion-of-a-day that Sheets secretly works in. Say the output date and time are in A2 and A3, then you could do simple addition of that 'duration' offset, the date, and the time; e.g. =A2+A3+MID(INDEX(SPLIT(A1," "),1,5),1,3)/24+(MID(INDEX(SPLIT(A1," "),1,5),1,1)&MID(INDEX(SPLIT(A1," "),1,5),4,5))/1440 …But even this makes the assumption you just want to naïvely 'apply' the offset.
    – Joel Reid
    Mar 12, 2019 at 14:31
1

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.

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.