I want to format a Google Sheet cell when I read it in my script but it comes out 25 minutes later.
The cell's time is 20:00:00 but the output is 20:25. I am using this

var formattedDate = Utilities.formatDate(time, "GMT+2", HH:mm");

Without using it the time comes as 1899-12-30T18:25:08.000Z even though the cell's content is 20:00:00. Any ideas?

  • Set your locale correctly in settings
    – TheMaster
    Jul 12, 2018 at 23:10

2 Answers 2


The way that Sheets and Apps Script handle "dates" are very different.

  • For Sheets the "date" unit is 1 day; The base date is 1899-12-30 0:00:00, getting the timezone from the spreadsheet settings.
  • For Apps Script, which is based on JavaScript, the "date" unit is 1 millisecond. The base date is 1970-1-1 00:00:00 UTC.

Below is an example script that if the active cell has 20:00:00 it will add "25:00" to the cell to the right.

function myFunction() {
  /* Assume that cell value is 8:00:00 pm.
  The displayed value depends on spreadsheet settings and cell format */
  var range = SpreadsheetApp.getActiveRange(); // See Apps Script documentation

  var value = range.getValue(); // This returns a date object;

  var time = value.getTime(); // See JavaScript documentation

  var delta = 25 * 60 * 1000; // Convert 25 minutes to milliseconds

  var newTime = time + delta; // Calculate new time

  var newDate = new Date(newTime); // Get new date object

  var spreadsheet = SpreadsheetApp.getActive(); // See Apps Script documentation
  var timezone = spreadsheet.getSpreadsheetTimeZone(); // See Apps Script documentation
  var format = 'HH:mm'; // Set date format for output
  var output = Utilities.formatDate(newDate, timezone, format); // This returns a string
  var target = range.offset(0, 1); // See Apps Script documentation
  target.setValue(output); // See Apps Script documentation

you are calling time inside date (formatDate) without actual date format so the sheet is guessing.

var formattedDate = Utilities.formatDate(time, "GMT+2", "MM-dd-yyyy HH:mm:ss");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.