Convert written out date (DD.MMMM.YYYY at hh:mm apm) string into date value-CodePudding

Unfortunately, the Google-Sheets sync extension saves the timestamp as a string. (Example: February 6, 2022 at 11:40 pm). Is there any way of automatically converting this automatically to an actual date? I tried the usual date conversion formulas but they dont work. Maybe a work-around?

CodePudding user response：

Suppose that your string-dates are in A2:A.

Place the following formula into the Row-2 cell of any other open column (e.g., B2):

=IF(A2:A="",,DATEVALUE(REGEXEXTRACT(A2:A,"^(. ),\s")) REGEXEXTRACT(A2:A,"\d :. $"))

CodePudding user response：

if a timestamp is in cell A2, try this:

=1*SUBSTITUTE(A2,"at","")

This just gets rid of the word "at" and then multiplying by 1 forces an attempt to convert it into a date value. Then you can just do a regular Format>Number>Date and Time on that cell and it should show how you'd want.