CODEWITHSUNDEEP
datetimegoogle-apps-scriptgoogle-sheetstimezone
Paul Hermans
Paul Hermans

Reputation: 65

Utilities.formatDate() gives wrong date/time

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

Upvotes: 1

Views: 269

Answers (1)

Cooper
Cooper

Reputation: 64062

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

Upvotes: 2

Related Questions