What I'm trying to do, is to list out time zones for 24 people. The problem comes in when daylight savings dates trigger, the listed time is no longer correct. Currently, I just have everyone's GMT value (+8, -5, etc) and then it calculates from there. But I'd love it if people can give me PST, EST, AEST, etc and then Google Sheets would know when daylight savings occurs, to update the current time zone. For example, California is currently -7, but after the end of September, it will be back at -8.

Here's a sheet for example: sample spreadsheet


you can try script for that:

 // Converts a datetime string to a datetime string in a targe timezone.
 //param {"September 1, 2018 1:00 PM PST"} datetimeString Date, time and timezone.
 //param {"GMT"} timeZone Target timezone
 //param {"YYYY-MM-dd hh:mm a z"} Datetime format
function myFunction(datetimeString,timeZone,format) {
  var moment = new Date(datetimeString);
  return Utilities.formatDate(moment, timeZone, format)

and then use formula like:

=myFunction("September 1, 2018 "&B2&"GMT","PST","hh:mm a")
  • That's not the problem. The problem is that various points in the year, the local time in California changes from GMT-8 to GMT-7, when California goes from PST to PDT and back. I want to open my sheet and see the correct current timezone. Jul 30 '18 at 21:35
  • @TamirNadav therefore that script... you will set the exact day when the change will occur and how time should be "corrected" then you just open your sheet on 8/31 and 9/1 and you will see the time has jumped to reflect timezone/time change etc.
    – user0
    Jul 30 '18 at 21:41

If you are not concerned that the spreadsheet knows the specific zone it is in (Pacific, Mountain, Central, Eastern, etc.) and the zone conforms to the normal xST/xDT rules (DT=Second Sunday in March through First Sunday in November) the following formula will do it, where "ZoneDate" is the target date. If you are looking for the current date and time to drive this you can replace "ZoneDate" with the today() function.

=IF(AND(ZoneDate>=DATE(YEAR(ZoneDate),3,1)+14-WEEKDAY(DATE(YEAR(ZoneDate),3,1)-1), ZoneDate < DATE(YEAR(ZoneDate),11,1)+7-WEEKDAY(DATE(YEAR(ZoneDate),11,1)-1)),"EDT","EST") 

This example is set up for Eastern time, just change the "EDT" and "EST" at the end of the conditional expression to the desired time zone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.