0

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

0

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
 //customfunction
 //
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. – Tamir Nadav 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

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.