How to read the correct time/duration values from Google Spreadsheet

Question

I'm trying to get from a time formatted Cell (hh:mm:ss) the hour value, the values can be bigger 24:00:00 for example 20000:00:00 should give 20000:

Table:

if your read the Value of E1:

var total = sheet.getRange("E1").getValue();
Logger.log(total);

The result is:

Sat Apr 12 07:09:21 GMT+00:09 1902

Now I've tried to convert it to a Date object and get the Unix time stamp of it:

  var date = new Date(total);
  var milsec = date.getTime();
  Logger.log(Utilities.formatString("%11.6f",milsec));
  var hours = milsec / 1000 / 60 / 60; 
  Logger.log(hours)

1374127872020.000000

381702.1866722222

The question is how to get the correct value of 20000 ?

Answer 1

Expanding on what Serge did, I wrote some functions that should be a bit easier to read and take into account timezone differences between the spreadsheet and the script.

function getValueAsSeconds(range) {
  var value = range.getValue();

  // Get the date value in the spreadsheet's timezone.
  var spreadsheetTimezone = range.getSheet().getParent().getSpreadsheetTimeZone();
  var dateString = Utilities.formatDate(value, spreadsheetTimezone, 
      'EEE, d MMM yyyy HH:mm:ss');
  var date = new Date(dateString);

  // Initialize the date of the epoch.
  var epoch = new Date('Dec 30, 1899 00:00:00');

  // Calculate the number of milliseconds between the epoch and the value.
  var diff = date.getTime() - epoch.getTime();

  // Convert the milliseconds to seconds and return.
  return Math.round(diff / 1000);
}

function getValueAsMinutes(range) {
  return getValueAsSeconds(range) / 60;
}

function getValueAsHours(range) {
  return getValueAsMinutes(range) / 60;
}

You can use these functions like so:

var range = SpreadsheetApp.getActiveSheet().getRange('A1');
Logger.log(getValueAsHours(range));

Needless to say, this is a lot of work to get the number of hours from a range. Please star Issue 402 which is a feature request to have the ability to get the literal string value from a cell.