2

Say, I have the values 01:00, 01:00, 00:30, 00:30, 01:00, 01:00, 00:30, 00:30, 01:00 in duration format (HH:MM) in cell E2 to E10.

The formula =SUM(E2:E10) returns 07:00 which is correct, but my following custom spreadsheet function written in Google Apps Script returns 07:54 which is incorrect.

How can I correctly calculate sum of the durations using a custom function?

function totalDuration() {
    var result    = 0;
    var durations = SpreadsheetApp.getActiveSheet().getRange("E2:E10").getValues();

    for(var i = 0; i < durations.length; i++) {
        var date = new Date(durations[i]);
        result  += (date.getHours() * 60) + date.getMinutes();
    }

    return result / 60 / 24;
}
A.N.M. Saiful Islam
  • 2,118
  • 5
  • 28
  • 34
  • Use split js function – Zig Mandel Jun 10 '14 at 13:04
  • This describes the problem: http://stackoverflow.com/questions/14363073/converting-google-spreadsheet-date-into-a-js-date-object ... You could use logging or the debugger to see the cause. Looks like the spreadsheet dates have a year of 1900 and when converted to JavaScript date, it is causing a problem. – eddyparkinson Jun 11 '14 at 00:47
  • possible duplicate of [GAS: How to read the correct time values form Google Spreadsheet](http://stackoverflow.com/questions/17715841/gas-how-to-read-the-correct-time-values-form-google-spreadsheet) – Eric Koleda Jun 19 '14 at 14:15
  • Please see [my answer](http://stackoverflow.com/questions/17715841/gas-how-to-read-the-correct-time-values-form-google-spreadsheet/17727300#17727300) on a similar question for sample code that simplifies this. – Eric Koleda Jun 19 '14 at 14:16

0 Answers0