Automatically convert date text to correct date format using Google Sheets

Question

I'm trying to convert date from "text" to correct format. It is logged to Google Spreadsheets and I'm unable to use it to plot graphs.

This is the text format: February 3, 2018, at 11:21 AM

Time is not relevant, all I need is the date converted: DD/MM/YYYY.

I found a similar question where Gary's Student answered with a formula that looks like this for a different format:

=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1)-2,2),""))

(link to that question)

How can I use above formula (or something similar) so that text is converted to date?

Thanks in advance.

Answer 1

The , at portion of the string is keeping Google Sheets from recognizing it as a datevalue. Just remove it with the substitute function and wrap in datevalue function like so: =DATEVALUE(SUBSTITUTE(A1,", at",""))

To format as DD/MM/YYYY just go to custom formatting and set it to look like the following:

Answer 2

=DATEVALUE(JOIN("/", LEFT(D5,2), {MID(D5,4,2), RIGHT(D5,4)}))

where D5 contains for example: 25.06.2019

which script converts to datevalue: 43641

Dateformat is as dd.MM.YYYY converted to dd/MM/YYYY and then converted to datevalue.

Google sheet's documentation helps:

DATEVALUE, JOIN, LEFT, MID, RIGHT

Datevalue is useful for organizing rows of data by date correctly.