Date Manipulation using new Date() & Utilities.formatDate throw Unexpected Results

Question

I have been struggling for the past a few hours by this weird behaviour from Google Sheet & Apps Script with date manipulation.

What I want to achieve & my current code is:

1. Get current date & time

var nowtime = new Date().getTime(); 
var nowdate = new Date(nowtime);
var formattedNow = Utilities.formatDate(nowdate, "Australia/Sydney", "dd/MM/yyyy HH:mm");

Logger.log(nowtime);
Logger.log(nowdate);
Logger.log(formattedNow);

Result:

1.659269747206E12
Sun Jul 31 22:15:47 GMT+10:00 2022
31/07/2022 22:15

2. Get a date & time forward 10 days

var thentime = nowtime + 1000 * 60 * 60 * 24 * 10; //that is 1000 milliseconds in a second, 60 seconds in a minute, 60 minutes in an hour, 24 hours in a day and 10 days 
var thendate = new Date(thentime);
var formattedThen = Utilities.formatDate(thendate, "Australia/Sydney", "dd/MM/yyyy HH:mm");

Logger.log(thentime);
Logger.log(thendate);
Logger.log(formattedThen);

Result:

1.660133747212E12
Wed Aug 10 22:15:47 GMT+10:00 2022
10/08/2022 22:15

Everything above to this point is all correct, now the weird behaviours:

3. Record both date/times in a corresponding cell

sheet.getRange("A1").setValue(formattedNow);
sheet.getRange("A2").setValue(formattedThen);

Value in cell A1 in the Google Sheet is

31/07/2022 22:15

But value in cell A2 in the Google Sheet is

10/8/2022 22:15:00

So cell A2 is not following the Utilities.formatDate already.

And when I try to extract the date value from both A1 & A2 by using following method:

var a1 = new Date(sheet.getRange("A1").getValue());
var a1date = Utilities.formatDate(a1, "Australia/Sydney", "dd/MM/yyyy HH:mm");
Logger.log(a1date);

var a2 = new Date(sheet.getRange("A2").getValue());
var a2date = Utilities.formatDate(a2, "Australia/Sydney", "dd/MM/yyyy HH:mm");
Logger.log(a2date);

Results are:

31/07/2022 22:15 //A1, correct
09/10/2022 09:15 //A2, INCORRECT

I thought it's maybe related to the issue described in this question: Google Apps Script date format issue (Utilities.formatDate) , but it's not really working, and there is something even more weird:

If I change the date manipulation to a number less than 1 day in milliseconds, for example, instead of doing

var thentime = nowtime + 1000 * 60 * 60 * 24 * 10

but to do this:

var thentime = nowtime + 1000 * 60 * 60 * 23 

All the outcomes would then be completely correct and as expected.

Then I did some more research on date manipulation thinking maybe I was doing the wrong way, I tried Google App Script Utilities.formatDate(new Date() adding days to today and it's still giving me the same issue.

---- Update 01/08/2022 ---- Below are complete code to reproduce the issue:

function onOpen(e) {

  const gsId = "Google Sheet ID"
  const gs = SpreadsheetApp.openById(gsId)
  const sheet = gs.getSheetByName("Sheet1")

  var nowtime = new Date().getTime();
  var nowdate = new Date(nowtime);
  var formattedNow = Utilities.formatDate(nowdate, "Australia/Sydney", "dd/MM/yyyy HH:mm");
  Logger.log(nowtime);
  Logger.log(nowdate);
  Logger.log(formattedNow);

  var thentime = new Date().getTime();
  thentime = thentime + 1000 * 60 * 60 * 24 * 10;
  var thendate = new Date(thentime);
  var formattedThen = Utilities.formatDate(thendate, "Australia/Sydney", "dd/MM/yyyy HH:mm");
  Logger.log(thentime);
  Logger.log(thendate);
  Logger.log(formattedThen);

  Logger.log("----everything above is showing correct results----");

  sheet.getRange("A1").setValue(formattedNow);
  sheet.getRange("A2").setValue(formattedThen);
  
  var a1 = new Date(sheet.getRange("A1").getValue());
  var a1date = Utilities.formatDate(a1, "Australia/Sydney", "dd/MM/yyyy HH:mm");
  Logger.log(a1date);

  var a2 = new Date(sheet.getRange("A2").getValue());
  var a2date = Utilities.formatDate(a2, "Australia/Sydney", "dd/MM/yyyy HH:mm");
  Logger.log(a2date);

}

Answer 1

Get time and date

function getDateAndTime() {
  const dt = new Date();
  var nowtime = Utilities.formatDate(dt,SpreadsheetApp.getActive().getSpreadsheetTimeZone(),"HH:mm:ss");
  var nowdate = dt;
  var formattedNow = Utilities.formatDate(nowdate, "SpreadsheetApp.getActive().getSpreadsheetTimeZone()", "dd/MM/yyyy HH:mm");
  Logger.log(nowtime);
  Logger.log(nowdate);
  Logger.log(formattedNow);
}

• Javascript Date Object

When using Date methods always refer to document to insure that you understand what is returned.