8

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.

TheIdiot
  • 113
  • 1
  • 1
  • 4
  • Are you using Google Sheets or Excel? You have tags for both, but your title says Google Sheets, but the link you included is to an Excel question. The two are similar but not the same. I would suggest (once you decide which platform you're using), just copy & paste your question's title into a search engine to find an answer. See also [mcve] and [ask], as well as [help/on-topic]. You can [edit] your question to provide clarification as needed,. – ashleedawg Feb 17 '18 at 09:58
  • Hi - I'm using Google Sheets. I've removed Excel tag. Thank you – TheIdiot Feb 18 '18 at 11:07

3 Answers3

10

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: enter image description here

enter image description here

Torey Price
  • 397
  • 5
  • 17
4
=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.

Neuron
  • 5,141
  • 5
  • 38
  • 59
J-ho
  • 238
  • 3
  • 17
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")
Lex
  • 567
  • 7
  • 7
  • Nice solution. Since I don't know who & what's behind the library key in the solution, I loaded moment.js from CDN as outlined here: https://stackoverflow.com/a/33315754/2258093 – Dave Gööck Jul 22 '21 at 15:57