Google Sheets DateTime static vs dynamic dates issues

Question

Summary

I have been working in Google Sheets with timezones and I have found a perplexing issue that I cannot figure out. Basically, if I use a custom script formula on a static date or a dynamically-generated date, I get different results, when it should be the same!

What I have

Here is a quick preview that shows what I am talking about. C2 is a dynamically-created Date while C5 is a static Date. I have created a custom function that will take a TimeDateString such as C2 and C5 and convert it to another Time Zone.

You can clearly see that D2 and D5 are different answers; however, they should be the same. This is the reason for this question. Is this a bug? Or am I missing something?

Spreadsheet Link

https://docs.google.com/spreadsheets/d/1HEFud8oqM-Yx0BR5SjyEG8yjGK-O0t8c6YcEfYtnLSA/edit?usp=sharing

Code

Custom Script Function

function DateTime(dateTimeString, timeZone, format) {

  if (format == null)   { format = "MMM dd 'at' hh:mma z" }
  if (timeZone == null) { timeZone = "GMT" }

  var date_time = new Date(dateTimeString);

  if(!isNaN(date_time.valueOf())) {
    return Utilities.formatDate(date_time, timeZone, format);
  } else {
    throw Utilities.formatString('Invalid Date String: "%s"', dateTimeString);
  }

}

C2 Formula

=CONCATENATE(Month($A2), "/", if(Day($A2) < 10, CONCAT("0", Day($A2))), "/", Year($A2), " ", Hour($B2), ":", if(Minute($B2) < 10, CONCAT("0", MINUTE($B2)), Minute($B2))) 

D2 Formula

=DateTime(C2)

D5 Formula

=DateTime(C5)

Answer 1

Different Timezones

The comment suggested by the user pnuts was absolutely related to the reason for my strange behavior. I had modified my Spreadsheet timezone to be GMT+0:00; however, my script timezone remained at GMT+9:00 After making these two the same value, my sheet and the script worked correctly.

There are actually 2 Timezones that you need to review, and they need to match!!

1. Spreadsheet's Timezone (File > Spreadsheet settings > Timezone)
2. Script's Timezone (File > Project properties > Timezone)

Note: these two Timezones have to match!!

Otherwise, you will get strange results as shown in the question.

Further Action You Can Do

Google has admitted that this is a common source of issues, I have created an issue in there Google App Script Issue Tracker and you can vote on this open issue if you have experienced this issue first-hand.

https://issuetracker.google.com/issues/73046652

---

Supporting Documentation & Citations

Google App Script Documentation

Google App Scripts Session documentation for the timezone information stated the following...

Spreadsheet time zones that differ from the script time zone are a frequent source of scripting bugs.

Pnuts' Comment

As Pnuts has stated in the comment to my question...

Timezone for the script is Tokyo. Seems your formula could be =text(A2+B2,"mm/dd/yyyy hh:mm")

Note 1: he stated that he didn't want to make his comment an answer and instead wanted me to write one.

Note 2: he was also correct in the A2 + B2 modification.