Change time zone for date/time data, in Google Spreadsheets

This is a follow-up post to “Change time zone for column of time data, in Google Spreadsheets“, which defined a function called convertTimeZone. This function programmatically got values (presumably formatted as a date time) from a range of cells in a Google Spreadsheet, then converted those values to another time zone. The function convertTimeZone was then used  in another post: “Import and auto-update RSS feed, and keep history, in Google Spreadsheets“. convertTimeZone did not work as a formula inside Google Spreadsheets.

In this post, I experiment with using convertTimeZone as a Google Spreadsheets formula.

Basically… the only thing that needed to be done was use the built-in function Utilities.formatDate(date, timeZone, format) and return its output.

/***
 * Converts a datetime to another time zone.
 * @param {string} dateIn a string formatted as a datetime, as described above
 * @param {string} timezone an Olson ID, enclosed by quotation marks
 * @return {string} a datetime object formatted as "EEE, MMM dd yyyy HH:mm:ss z"
 ***/
function convertTimeZone(dateIn, timezone) {
  var dateOut = Utilities.formatDate(new Date(dateIn), timezone, "EEE, MMM dd yyyy HH:mm:ss z");
  return dateOut;
}

After adding this code snippet into the Script Editor of a Google Spreadsheet, the function =convertTimeZone("A1", "GMT") can be used, where "A1" and "GMT" are example parameters.

I honestly thought it was going to be this simple.  It’s not. Especially when dealing with dates and times, and especially when working with them in a spreadsheet app (any spreadsheet app).

Some examples:

[table width=”100%”]
#,date/time[attr style=”align:left;”],formula[attr style=”align:left;”],convertTimeZone[attr style=”align:left;”],status[attr style=”align:left;”]
1,9/5/2012 14:00:00,”=convertTimeZone($A1, ""Asia/Bangkok"")“,”Thu, Sep 06 2012 04:00:00 ICT”,Depends
2,9/5/2012 14:00:00 EDT,”=convertTimeZone($A2, ""Asia/Bangkok"")“,”Thu, Sep 06 2012 01:00:00 ICT”,CORRECT
3,9/6/2012 4:00:00 ICT,”=convertTimeZone($A3, ""America/Lima"")“,”Wed, Dec 31 1969 19:00:00 PET”,Does not work
4,”9/6/2012 4:00:00 “”Asia/Bangkok”””,”=convertTimeZone($A4, ""America/Lima"")“,”Wed, Dec 31 1969 19:00:00 PET”,Does not work
5,9/6/2012 4:00:00 Asia/Bangkok,”=convertTimeZone($A5, ""America/Lima"")“,”Wed, Dec 31 1969 19:00:00 PET”,Does not work
6,9/6/2012 4:00:00 TH,”=convertTimeZone($A6, ""America/Lima"")“,”Thu, Sep 06 2012 06:00:00 PET”,Wrong!
7,9/6/2012 4:00:00 GMT+07:00,”=convertTimeZone($A7, ""America/Lima"")“,”Wed, Sep 05 2012 16:00:00 PET”,CORRECT
8,9/5/2012 14:00:00 GMT-07:00,”=convertTimeZone($A8, ""Asia/Bangkok"")“,”Thu, Sep 06 2012 04:00:00 ICT”,CORRECT
9,31/1/2012 14:00:00,”=convertTimeZone($A9, ""Asia/Bangkok"")“,”Wed, Jul 02 2014 04:00:00 ICT”,Wrong input date format
[/table]

As you can see, there are several caveats to using Utilities.formatDate. All of the ones I found dealt with how the input date/time is formatted. This was not previously a problem for me because I was using Utilities.formatDate on date/times automatically generated from RSS feeds. These date/times were pretty strictly formatted. Human-inputted date/time formats are much less predictable.

Here are some caveats I discovered:

1. Input date must be formatted "MMM dd yyyy" or "mm/dd/yyyy".
2. Input must contain a date, or “Jan 1, 1970” is assumed.
3. Input time must be formatted "HH:mm", "hh:mm a", where a denotes AM/PM.
4. Input time format can include seconds or time zone, e.g. "HH:mm:ss z", "hh:mm a z", where z denotes time zone.
5. If input time does not include a time zone, the spreadsheet’s time zone at creation (mentioned before) is used.
6. Timezone must be a Olson ID: http://unicode.org/repos/cldr/trunk/docs/design/formatting/zone_log.html

Regarding Caveat #1 — incidentally, trying the date format dd/mm/yyyy, e.g. 31/1/2012, does produce a result, as observed in Example #9 above. But the output is incorrect.

Caveat #5 makes the spreadsheet less portable, i.e. this “time zone at creation” does not change even if the Spreadsheet’s time zone setting is changed or browser’s time zone is different (e.g. when spreadsheet is shared with someone in a different time zone). (I may be wrong about this, since I only did a few simple tests on a single browser without clearing cache, etc.)

Caveat #6 makes for the biggest headache. There are several different ways to denote time zones, and time zone with daylight savings. In the examples above, I tried several of these methods. Only GMT+hh:mm produces consistently correct results. If the time zone is not recognized, then Utilities.formatDate considers it as any other non-date/time string, and considers “Jan 1, 1970 GMT+0:00” instead.

Validation and Google Spreadsheets Data Types

It may be possible to circumvent some potential pitfalls with some pre-formatting or validation helper functions. After all, the function above is a very bare wrapper to allow Utilities.formatDate to be used directly as a spreadsheet formula! Writing or using these helper functions doesn’t seem trivial, however, at least to me.

Trying some simple validation led to some surprises.

In particular, though the Google Apps Script documentation will mention a Date type, I couldn’t figure out how to check this, e.g. by using if ( typeof dateIn != "date") or if ( dateIn instanceof Date ). I also found a function from StackOverflow #1353684, which did not work. In fact, using the checkA1Value() function below to check data types, the only two data types I could get from Google Spreadsheets were number and string! Perhaps I’m missing or misunderstanding something. It doesn’t seem like the number data type is broken down any further, e.g. into int or float.

Additionally, values of either string or number data types could get a NumberFormat. In other words, all values have a numeric format even if they are strings (?). Kinda perplexing, but kinda makes some sense when considering that these are values from a spreadsheet (e.g. as opposed to a word document).

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var r = SpreadsheetApp.getActiveRange();

/***
 * Sends value, type, and number format of cell "A1" of active sheet to Log
 * @see function isValidDate, which will always return false for any spreadsheet cell value
 ***/
function checkA1Value() {
  var value = s.getRange("A1").getValue();
  var isDate = isValidDate(value);
  var typeOf = typeof value;
  var getNumberFormat = s.getRange("A1").getNumberFormat();
  Logger.log("Value: " + value + "\nType: " + typeOf + "\nNumber Format: " + getNumberFormat);
}

Other Quick Functions

Note: I haven’t found a way to find a Google Spreadsheet’s “time zone at creation” value.

/***
 * Gets spreadsheet time zone
 * @return {string} spreadsheet time zone
 ***/
function getSpreadsheetCurrentTimeZone() {
  return SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
}

/***
 * Gets session time zone
 * @return {string} session time zone
 * @return "#ERROR" when used as a formula 
 * @throws "error: You do not have permission to call getTimeZone (line 34) if called as a formula" when used as a formula
 ***/
function getSessionTimeZone() {
  Logger.log(Session.getTimeZone());
  return Session.getTimeZone();
}

/***
 * Returns true if date is valid
 * http://stackoverflow.com/questions/1353684/detecting-an-invalid-date-date-instance-in-javascript
 * Does not work on Google Spreadsheet objects?
 * @param {string} d date string to be tested
 * @return {boolean} false
 ***/
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}