Wrong time extraction in google spreadsheets?

Question

I'm trying to retrieve the time value from google sheet cell via GAS. I put in the cell value (10:30) and get it with the following code (video)

function test_time_getting() {
  var sheet, value, p;
  sheet = SpreadsheetApp.getActive().getSheetByName('mockup data');
  value = sheet.getRange(1,1).getValue();
  Logger.log(value);
}

But unexpectedly I received next result in the log:

[15-09-27 05:07:43:784 PDT] Sat Dec 30 10:26:59 GMT+02:27 1899
How it could happen and what to do to retrieve proper time value?

dup of many others in stackoverflow like the linked one. – Zig Mandel Sep 27 '15 at 13:54 — Zig Mandel, Sep 27 '15 at 13:54

score 0 · Answer 1 · edited May 23 '17 at 11:44

Right time extraction makes by following code:

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);
}

Thus you get the right quantity of seconds then you can calculate quantity of hours and minutes:

  seconds = getValueAsSeconds(range);
  hours = Math.floor(seconds/3600);
  minutes = (seconds/60) % 60;

The answer was found here

Wrong time extraction in google spreadsheets?

1 Answers1