add time from cells programmatically

Question

I have a a spreadsheet which I use to note how I spent my time on a project. In that spreadsheet I have a couple of columns, one of which is the time spent doing something and the other is the category of what that something is (for example meetings or accounting or calling customers). I am trying to write a script which I pass the name of the category and it then loops though all the rows to see if the category equals the category I passed it, and if so it adds the time to the counter. I am however having trouble adding the time together. What I have so far is this:

function getTimeInCat(cat){
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var time = new Date();
  var counter = 0;

  for(var i = 6; i < numRows; i++){
    var carCell = "F" + i;
    var cellName = "E" + i;
    if(sheet.getRange(i, 6).getValue() == cat){
      time += sheet.getRange(i, 5).getValue();
      counter++;
    }
  }
  return time;
}

Instead of what I want I get this in return:

Thu Jan 30 2014 18:29:22 GMT-0000 (GMT)Sat Dec 30 1899 00:50:39 GMT-0000 (GMT)Sat Dec 30 1899 00:05:39 GMT-0000 (GMT)

EDIT: It is giving the right amount of rows (The remnants of that test are still in the code)

Answer 1

Try var time = 0. Date() I think will transform your time to a date.

Answer 2

The values of those cells are actually Date objects. This is why you're getting a long string of timestamps when you add the values of the cells together. For whatever reason, Google decided that when you're entering a duration, it will actually be an offset of HH:mm:ss after 12/30/1899. Try changing the format of those times to a date (Format > Number > Date) and you'll see it changes to that date.

Luckily, with Date objects, you can use the getTime() function to get your total duration. getTime() gives you the number of milliseconds since 1/1/1970 so it will actually return very large negative numbers for those cells but that's easy enough to manage. You just need to create a Date object with 12/30/1899 as the date and subtract that from the value of the cell you want.

var base = new Date("12/30/1899").getTime(); // -2.2091436E12
var cell = sheet.getRange(row,col).getValue().getTime(); // some other large negative number
var delta = cell - base; // this should be your duration in milliseconds