Getting the number equalent of duration using GAS

Question

I have a cell that has the value of type duration, I have set the value to 00:10:00 which is 10 minutes, When I change the format of the cell to number it will show 0.01.

How will I get programmically using apps script the decimal equivalent of using apps script code?

00:10:00 is 0.01 10:00:00 is 0.42

What is the calculation used to convert this duration string?

Answer 1

How about this answer?

Values of 0.01 and 0.42 are the serial number. But in this case, when each cell is seen, 0.01 and 0.42 are 0.00694444444444444 and 0.416666666666667. You can also confirm this.

When you want to convert 0.00694444444444444 and 0.416666666666667 to 00:10:00 and 10:00:00, respectively, please do the following flow.

1. Convert serial number to unix time.
2. Convert unix time to date object.
3. Retrieve the value like 00:10:00 and 10:00:00.

Sample script:

var serialNumbers = [0.00694444444444444, 0.416666666666667];

for (var i = 0; i < serialNumbers.length; i++) {
  var unixTime = (serialNumbers[i] - 25569) * 86400 * 1000; // Reference: https://stackoverflow.com/a/6154953
  var dateObject = new Date(unixTime);
  var value = dateObject.toISOString().split("T")[1].split(".")[0];
  console.log(value); // 00:10:00 and 10:00:00
}

References:

• Unix time
• DATEVALUE function
• About var unixTime = (serialNumbers[i] - 25569) * 86400 * 1000

If this was not the result you want, I apologize.

Edit 1:

When the string like "00:10:00", "10:00:00" is converted to the serial number, how about the following script? In this script, the flow is as follows.

1. Convert string to date object.
2. Convert date object to unix time.
3. Convert unix time to serial number like 0.006944444445252884 and 0.4166666666678793.

var durations = ["00:10:00", "10:00:00"];
for (var i = 0; i < durations.length; i++) {
  var ISO8601 = "1899-12-30T" + durations[i] + "Z";
  var dateObject = new Date(ISO8601);
  var serialNumber = (dateObject.getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953
  console.log(serialNumber); // 0.006944444445252884 and 0.4166666666678793
}

Edit 2: Using values from cells retrieved using getValues()

• 00:10:00 and 10:00:00 are put in cells of Spreadsheet.
• Values from above cells are retrieved using getValues().
• From the retrieved values, you want to retrieve the serial number.

I could understand about your additional question. If my understanding is correct, how about this sample script?

As the point of this situation, I think that the time difference is required to be considered. Because the values retrieved by getValues() have already been the date object with the time difference. So in this case, this offset is required to be removed.

In this sample script, it supposes that the cells "A1" and "A2" has 00:10:00 and 10:00:00 as the duration. The flow of this sample script is as follows.

1. Retrieve values from cells using getValues().
2. Retrieve the time difference.
3. Remove the time difference from the retrieved values.
4. Retrieve serial number.

Sample script:

var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A1:A2").getValues();
var offset = (new Date()).getTimezoneOffset(); // Retrieve the time difference.
for (var i = 0; i < values.length; i++) {
  var dateObject = new Date(values[i][0].getTime() - (1000 * 60 * offset));
  var serialNumber = (dateObject.getTime() / 1000 / 86400) + 25569; // Reference: https://stackoverflow.com/a/6154953
  Logger.log(serialNumber) // 0.006944444445252884 and 0.4166666666678793
}