Datetime with timezone in Google Sheets?

Question

I want calculate time differences between date times in different timezones in Google Sheets.

If I format two fields as "datetime" and another as "duration", I can successfully calculate the difference within the same timezone using the difference operator.

Example: A1= 1/10/2016 10:10:00, B2 = 13/11/2016 15:35:00 C2 = =B2-B1.

However when I add the time zone to a date time, e.g. A1= 1/10/2016 10:10:00 GMT+1:00:00, C2 displays #VALUE.

I know I can calculate the time zone difference myself and subtract it from the duration, but is there any way of automating that by specifiying the time zone directly in the datetime fields?

Answer 1

Short answer

Use a custom function.

Explanation

Google Sheets formulas and date formats don't include a timezone handler, so if you include a time zone symbol this will make that the value be considered a string instead of a date-time.

An alternative is to use Google Apps Script / JavaScript to write a function that converts a date-time string including a time zone to a Google Sheets date.

Example

/**
 * Converts a string to a date-time value
 *
 * @param {Thu Apr 23 2015 00:00:00 GMT+0200} dateTimeString
 * @customfunction
 */
function stringToDateTime(dateTimeString) {
  var output = new Date (dateTimeString);
  if(!isNaN(output.valueOf())) {
    return output;
  } else {
    throw new Error('Not a valid date-time');
  }   
}

A1 = 1/10/2016 10:10:00 GMT+0100

Note: The date-time format must be supported by JavaScript Date Object

=B1-stringToDateTime(A1)

References

• How to read the correct time values from Google Spreadsheet