Automatically convert date text to correct date format using Google Sheets
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.
- Open
tools
→script editor
in menu to open script editor in new tab - Click
Untitled project
in top left corner and rename - Open
Resources
→Libraries
in top menu - Paste library key
MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48
and clickAdd
to add theMoment
library to your script - Choose
Moment
version9
and clickSave
- 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")