Timezone conversion in a Google spreadsheet
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
I had the same problem (convert Manila Time to Sydney Time and automatically adjust for daylight saving time) when I found this page.
I didn't want to have a custom function but I found that, in Sydney, AEST (Australian Eastern Standard Time) starts on the first Sunday of April and AEDT (Australian Eastern Daylight Time) starts on the first Sunday of October.
So I thought, if I could find a formula that detects whether a date falls between the first Sunday of April and first Sunday of October (Standard Time) then I can automatically add 1 hour to the usual 2 hours to Manila time during Daylight Saving Time (dates falling outside the two dates) to have Sydney Time.
These two Excel solutions worked fine in Google Sheets:
- How You Can Determine the First Sunday in a Month in Excel
- How to determine if a date falls between two dates or on weekend in Excel
First Sunday of April this year (A1):
=CONCATENATE("4/1/",Year(today()))+CHOOSE(WEEKDAY(CONCATENATE("4/1/",Year(today())),1),7,6,5,4,3,2,1)
First Sunday of October this year (A2):
=CONCATENATE("10/1/",year(today()))+CHOOSE(WEEKDAY(CONCATENATE("10/1/",year(today())),1),7,6,5,4,3,2,1)
DST detector (A3) — if a date falls outside these two dates, it's DST in Sydney:
=IF(AND(today()>A1,today()<A2),"AEST","AEDT")
Time in Sydney (A4):
=NOW()+TIME(IF(A3="AEDT",3,2),0,0)
NOW() can be changed to any time format for tabulation:
I'm a new contributor and a novice, but I stumbled upon a function that had not been mentioned despite many hours of searching on the Sheets/Time Zone issue. Hoping this relatively simple solution will help.
For my sheet, I just want to add a row and automatically populate the local sheet date and time in the first two cells.
The .getTimezoneOffset() returns the difference in minutes between the client TZ and GMT, which in NY during Daylight Savings Time is 240. The function returns a positive number for the zones with "GMT-x", and vice versa for zones with "GMT+x". Hence the need to divide by -60 to get the correct hour and sign.
function addRow() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
sheet.insertRows(2, 1);
rightNow = new Date();
var tzOffset = "GMT" + rightNow.getTimezoneOffset() / -60;
var fDate = Utilities.formatDate(rightNow, tzOffset, "MM-dd-yyyy");
var fTime = Utilities.formatDate(rightNow, tzOffset, "HH:mm");
sheet.getRange('A2').setValue(fDate);
sheet.getRange('B2').setValue(fTime);
sheet.getRange('C2').setValue(tzOffset);
}
I've since found that I'm not the first person to respond to the GMT correction connundrum mentioning .getTimezoneOffset(). However, this thread has the most views on this topic, so I figured this option deserves a mention.
DST ends here on November 7th, 2021, so I'll report back if it doesn't adjust as expected to "GMT-5"
.getTimezoneOffset()
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);
}
