Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have lots of dates in the first column in the format

August 23, 2010 at 11:01PM

I was wondering if anyone knows a way to get them formatted in a way that I can then use for date and time calculations. Ideally the function will only take up an extra column.

share|improve this question

migrated from superuser.com Sep 15 '13 at 12:10

This question came from our site for computer enthusiasts and power users.

    
I don't believe there is a built in function that will format the date in the way that you want it to. Google Drive has a built-in Script Editor (Tools - Script Editor) you can use to write a custom script to do what you want. You could post a question on StackOverflow if you get stuck. –  yassarikhan786 Sep 11 '13 at 18:33

2 Answers 2

up vote 2 down vote accepted

The following custom function will transform the string date notation into a date notation:

Code

function myDate(range) {
  var output = [];  
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var rSplit = range[i][0].split(" "), corr = 0;
    if(rSplit[4].substring(5,7) == "PM") {
      corr = 10;
    }   
    var month = parseInt(new Date(Date.parse(rSplit[0]+"1,2013")).getMonth());
    var day = parseInt(rSplit[1].replace(/,/g, ""));
    var year = parseInt(rSplit[2]);
    var hour = parseInt(rSplit[4].substring(0,2))+corr;
    var min = parseInt(rSplit[4].substring(3,5),10);    
    var nDate = new Date(year, month, day, hour, min);
    output.push([nDate]);
  } 
  return output;
}

Remark

Correction is made to account for AM/PM. No correction for GMT. Add this script via the Tools menu in the spreadsheet and select Script editor. Save the script and you are ready to go !!

Usage

myDate(A1:A4)

Example

I've created an example file for you: string date to date

share|improve this answer
    
I believe corr should equal 12however as the edit would be less then 6 characters i cant change it –  jamesmstone Sep 15 '13 at 22:08
    
After testing the code. I noticed for the time 11:09PM (regardless of the year) it always returns 1/1/1970 11:00:00. So for example August 23, 2010 at 11:09PM returns 1/1/1970 11:00:00 do you have any idea why? –  jamesmstone Sep 15 '13 at 22:33
1  
I have updated @Jacob 's example sheet to reflect this –  jamesmstone Sep 15 '13 at 22:40
    
@jamesmstone see revised code, it works for the example in the spreadsheet.I had to force the parseInt(text, 10) to decimals. The corr is set at corr=10 because this will yield the same time. I did not take into account the different time zones. –  Jacob Jan Tuinstra Sep 16 '13 at 12:24
    
Thanks. Quick question is the function parseInt() the same as the javascript parseInt() . do google macros use javascript? –  jamesmstone Sep 16 '13 at 22:05

You can also use one of the display format scripts, this keeps the underlying numbers the same as normal display formats and as such won't corrupt any date/time numerical calculations.

so a script search for 'Format' and get one of the number and date ones, the format to be used is the same as the =Text(,"") variation without needing to be in sheet converted.

MMMM d, yyyy at h:mma

For:

August 23, 2010 at 11:01PM

For other formatting options see: http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.