Timezone conversion in a Google spreadsheet

by

Short answer

There is no built-in function but you could build a custom function.

Example

/**
 * Converts a datetime string to a datetime string in a targe timezone.
 *
 *@param {"October 29, 2016 1:00 PM CDT"} datetimeString Date, time and timezone.
 *@param {"Timezone"} timeZone Target timezone
 *@param {"YYYY-MM-dd hh:mm a z"} Datetime format
 *@customfunction
 */
function formatDate(datetimeString,timeZone,format) {
  var moment = new Date(datetimeString);
  if(moment instanceof Date && !isNaN(moment)){
    return Utilities.formatDate(moment, timeZone, format)
  } else {
    throw 'datetimeString can not be parsed as a JavaScript Date object'
  }
}

NOTE:

new Date(string) / Date.parse(string) implementation in Google Apps Script doesn’t support some timezones abbreviations.

From https://tc39.es/ecma262/#sec-date-time-string-format

There exists no international standard that specifies abbreviations for civil time zones like CET, EST, etc. and sometimes the same abbreviation is even used for two very different time zones.

Related

  • Get UTC offset from timezone abbreviations

Explanation

In order to consider daylight saving time zones the input argument for of the value to be converted should include the date, no only the time of the day. You could set a default date and time zone to build the datetimeString by concatenating it before calling the formula.

=formatDate("October 29, 2016 "&A2&" GMT","PDT","hh:mm a")

For the target timezone besides the three letter abbreviation we could use TZ database names like America/Los_Angeles, example:

=formatDate("October 29, 2016 "&A2&" GMT","America/Los_Angeles","HH:mm")

If timezone abbreviation and TZ name fails for the datetimeString use time offsets (i.e. UTC-4).

See also

  • Calculating year, month, days between dates in google apps script

References

  • https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Date

Leave a Comment