Date string cannot be parsed into date/time in Google Sheets

Question

I have an IFTTT integration that writes date strings to a sheet. I'm trying to filter the sheet by days of the week, but in order to do that, I need to be able to convert the date strings into actual dates.

The dates are formatted like this:

August 12, 2019 at 08:40PM

But when I feed it into DATEVALUE, it cannot parse it. I've tried removing the leading 0 before 08:40PM and adding a space between the minute and PM, but no luck.

Answer 1

The problem is the "at ".

Use SUBSTITUTE:

=DATEVALUE(SUBSTITUTE(A1,"at ",""))

If you need the time portion as well, you can use TIMEVALUE:

=DATEVALUE(SUBSTITUTE(A1,"at ",""))+TIMEVALUE(SUBSTITUTE(A1,"at ",""))