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.

Answer 3

Another solution is to create custom function.

1. Open tools ? script editor in menu to open script editor in new tab
2. Click Untitled project in top left corner and rename
3. Open Resources ? Libraries in top menu
4. Paste library key MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48 and click Add to add the Moment library to your script
5. Choose Moment version 9 and click Save
6. Paste function

function parseDate(dateString, format){
  return Moment.moment(dateString, format).toDate()
}

to Code.gs and save project Ctrl+S

Now you use your function in any cell in your sheet:

=parseDate(B2,"MMM D, YYYY, at HH:mm A")

more details about format: https://momentjs.com/docs/#/parsing/string-formats/

You can also create function to display date in custom format:

function formatDate(date, format){
  return Moment.moment(date).format(format)
}

Use it like this in cell

=formatDate(B5,"DD/MM/YYYY")

or

=formatDate(parseDate(B2,"MMM D, YYYY, at HH:mm A"),"DD/MM/YYYY")