Google电子表格中的时区转换 [英] Timezone conversion in a Google spreadsheet

问题描述

我知道这看起来很简单。

I know this looks simple.

在Google电子表格中，我有一列在一个时区（GMT）中输入时间
应该自动获取其他时区（太平洋时间）的时间。

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.

推荐答案

简短答案

没有内置函数，但是您可以构建自定义函数。

Short answer

There is no built-in function but you could build a custom function.

/**
 * 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'
  }
}

注意：

新日期（字符串） / Date.parse（字符串）在Google Apps脚本中的实现不支持某些时区缩写。

new Date(string) / Date.parse(string) implementation in Google Apps Script doesn't support some timezones abbreviations.

来自 https://tc39.es/ecma262/#sec-date-time-string-format

不存在国际标准指定了CET，EST等民用时区的缩写，有时甚至在两个截然不同的时区中也使用了相同的缩写。

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

• 从时区缩写中获取UTC偏移量
>


Related

• Get UTC offset from timezone abbreviations

为了考虑夏令时，要转换的值的输入参数应该包括日期，而不仅要包括时间那天。您可以设置默认的日期和时区，以通过在调用公式之前将其连接起来来构建datetimeString。

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")

目标时区，除了三个字母缩写外，我们可以使用 TZ数据库名称，例如 America / Los_Angeles ，例如：

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")

如果时区缩写和TZ名称失败对于datetimeString使用时间偏移量（即UTC-4）。

If timezone abbreviation and TZ name fails for the datetimeString use time offsets (i.e. UTC-4).



• 计算goo中日期之间的年，月，日gle apps脚本


• Calculating year, month, days between dates in google apps script


• https://developer.mozilla.org/en/docs/Web/JavaScript/Reference / Global_Objects / Date


• https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Date

这篇关于Google电子表格中的时区转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！