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?

Sheets dates don't really take different timezones into consideration, it all depends on and is based on the Time zone set in the spreadsheet settings. — Robin Gertenbach, Sep 27 '16 at 11:37

score 11 · Accepted Answer · edited May 23 '17 at 11:46

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

Datetime with timezone in Google Sheets?

1 Answers1

Short answer

Explanation

Example

References

Linked