Automatically Convert Date Text To Correct Date Format Using Google Sheets

Answer :

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:

=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.

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