Date and time format

Question

I have lots of dates in the first column in the format output by IFTTT:

August 23, 2010 at 11:01PM

Is there 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.}

Answer 1

I use this formula to convert the date time values IFTTT spits out to a useful format:

=VALUE(SUBSTITUTE(A1, " at ", " "))

^{Where A1 is the cell containing the date string}

This gives you a decimal number that you can format as "Date Time".

Answer 2

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

Answer 3

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