Search code examples
google-sheetsgoogle-apps-scripttimezoneformuladst

Timezone conversion in a Google Sheets with DST (Daylight savings)

I know this looks simple.

In a Google spreadsheet, I have a column where I enter time in one timezone (GMT)

And another column should automatically get time in another time zone (Pacific Time)

GMT PT
5:00 AM 9:00 PM

As of now, I am using:

=$C$3-time(8,0,0)

The problem here is, I want to change the time formula for Daylight savings.

Is there any function or script available which can take the daylight saving into calculation automatically.

Solution

  • 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

    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

    References