0

GS code comes back one day off when getting date from 3/9/2020 - 4/5/2020

All dates between 3/9/2020 - 4/5/2020 comeback incorrect.

  1. Google sheet add date column with date 3/9/2020
  2. Add code to gs below
  3. Comes back: Sun Mar 08 2020 23:00:00 GMT-0600 (CST)

3/9/2020 Sun Mar 08 2020 23:00:00 GMT-0600 (CST)

4/5/2020 Sat Apr 04 2020 23:00:00 GMT-0600 (CST)

 var data = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getValues();
 SpreadsheetApp.getUi().alert(data[0][0]);

Here is the google sheet: link

Zealous
  • 41
  • 6
  • 1
    Daylight savings? – Tedinoz Oct 25 '19 at 01:17
  • I'm taking dates from a Google sheet to create events in Google Calendars, how would I handle that if the days are wrong? – Zealous Oct 25 '19 at 01:49
  • 1
    Just on a tangent... are you aware that that a spreadsheet and its project each have their own Time Zone settings and they are NOT linked? So, one can change the timezone for a spreadsheet, but the project time zone will remain unchanged (until it is changed as well). – Tedinoz Oct 25 '19 at 07:48
  • 1
    Are these date/times in your own time zone, or a different time zone? If your own time zone, then you've got a difference between Spreadsheet and Project. I am in Australia (GMT+10) but I created a new spreadsheet and then set Spreadsheet Locale = "United States" and Time zone = "(GMT-06:00) Central Time"; also changed Project properties Time zone = "(GMT-06:00) Central Time". Entered 3/9/2020 in cell A1. Date/Time formatting = "3/9/2020 0:00:00"; Log = "Mon Mar 09 00:00:00 GMT-05:00 2020"; Alert = "Mon Mar 09 2020 00:00:00 GMT-0500 (CDT)". – Tedinoz Oct 25 '19 at 08:24
  • 1
    @AlanWells in a comment on [Time Zone in script is and in Google Sheets is different than Google Calendar](https://stackoverflow.com/q/52542141/1330560) reminds us that the Calendar also has its own Time Zone. His comment is worth reading. – Tedinoz Oct 25 '19 at 08:29
  • Anyone should be able to reproduce this in a new sheet. This is directly from Google sheets pulling the cell value in script editor. Here is the sheet in quest: https://docs.google.com/spreadsheets/d/15Bfp94UfYvhKW2VGfuPLkFH6akA9F-X0974d1HyQ4PU/edit?usp=sharing – Zealous Oct 25 '19 at 11:01
  • Its just the code above, I'm just using the debugger to get the value. I checked the time zone and both are for CST. Here is the link: https://docs.google.com/spreadsheets/d/15Bfp94UfYvhKW2VGfuPLkFH6akA9F-X0974d1HyQ4PU/edit#gid=0 – Zealous Oct 25 '19 at 11:18

1 Answers1

1

The dates come back correctly if I format it using GMT.

var formattedDate = Utilities.formatDate(data[0][0], "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");

UTC, GMT and Daylight Saving Time Neither UTC nor GMT ever change for Daylight Saving Time (DST). However, some of the countries that use GMT switch to different time zones during their DST period.

For example, the United Kingdom is not on GMT all year, it uses British Summer Time (BST), which is one hour ahead of GMT, during the summer months.

Zealous
  • 41
  • 6