Google-sheets – How to auto-calculate time based on time zones

formulasgoogle sheetstime zone

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

Best Answer

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")
Related Topic