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: 

![two Google Spread Sheet](https://i.stack.imgur.com/cgPfp.png)

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: 
There are two new functions `getDisplayValue()` and `getDisplayValues()` that returns the datetime or anything exactly the way it looks to you on a Spreadsheet. Check out the documentation [here](https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalue)
Make a cell somewhere with a duration value of "00:00:00". This cell will be used as a reference. Could be a hidden cell, or a cell in a different sheet with config values. E.g. as below:

[![enter image description here](https://i.stack.imgur.com/cbbER.png)](https://i.stack.imgur.com/cbbER.png)

then write a function with two parameters - 1) value you want to process, and 2) reference value of "00:00:00". E.g.:

```
function gethours(val, ref) {

  let dv = new Date(val)
  let dr = new Date(ref)

  return (dv.getTime() - dr.getTime())/(1000*60*60)

}

```

Since whatever Sheets are doing with the Duration type is exactly the same for both, we can now convert them to Dates and subtract, which gives correct value. In the code example above I used .getTime() which gives number of milliseconds since Jan 1, 1970, ... .

If we tried to compute what is exactly happening to the value, and make corrections, code gets too complicated.

One caveat: if the number of hours is very large say 200,000:00:00 there is substantial fractional value showing up since days/years are not exactly 24hrs/365days (? speculating here). Specifically, 200000:00:00 gives 200,000.16 as a result.