Timezone conversion in a Google spreadsheet

Question

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.

Answer 1

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

Answer 2

This tutorial was amazingly helpful: https://davidkeen.com/blog/2017/01/time-zone-conversion-in-google-sheets/

Google Sheets does not have a built in way of converting time zone data but by using the power of Moment.js and Google’s script editor we can add time zone functionality to any sheet.

These are the files I copied into my script project:

• https://momentjs.com/downloads/moment-with-locales.js saved as moment.js
• https://momentjs.com/downloads/moment-timezone-with-data.js saved as moment-timezone.js

Make sure you add the moment.js script first and have it above the moment-timezone.js script because moment-timezone.js depends on it.

Then in your other script project, your Code.gs file can look like this:

var DT_FORMAT = 'YYYY-MM-DD HH:mm:ss';  /** https://stackoverflow.com/questions/34946815/timezone-conversion-in-a-google-spreadsheet/40324587 */ function toUtc(dateTime, timeZone) {     var from = m.moment.tz(dateTime, DT_FORMAT, timeZone);//https://momentjs.com/timezone/docs/#/using-timezones/parsing-in-zone/   return from.utc().format(DT_FORMAT); }  /** https://stackoverflow.com/questions/34946815/timezone-conversion-in-a-google-spreadsheet/40324587 */ function fromUtc(dateTime, timeZone) {   var from = m.moment.utc(dateTime, DT_FORMAT);//https://momentjs.com/timezone/docs/#/using-timezones/parsing-in-zone/   return from.tz(timeZone).format(DT_FORMAT); }