Utilities.formatDate() gives wrong date/time

Question

I read a cell in a google sheet which is a date (it reads it correctly) and then I attempt to reformat it using the Utilities.formatDate() method. All I really want to do is drop the "minutes" from the time so it looks like:

9/11/2023 10:02 am

In debug mode, the date is correct when read, but the formatDate method gives the wrong results.

I looked at timezone in both Sheets and AppScript - and there is no way to make them the same (that I see) because the list of choices is different in the two platforms. Not sure what to do.

I am trying to use GMT-4 Eastern Standard Time / New York which I can do fine in app script but there is no such one listed in Google Sheets.

Any advice would be welcome

user30hu · Answer

You can probably resolve you issue with just formatting the cell. Using information from here

Here's a simple script with same date and different formats

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.getRange("A1:D1").setValue(new Date()).setNumberFormats([["mm/dd/yy hh:mm:ss", "mm/dd/yyyy hh:mm", "mm/dd/yyyy hh:mm AM/PM","mmm dd, yyyy hh:mm am/pm"]]);
}

A	B	C	D
09/11/22 16:43:40	09/11/2022 16:43	09/11/2022 04:43 PM	Sep 11, 2022 04:43 PM

Utilities.formatDate() gives wrong date/time

1 Answers