在Google Sheets单元格中将ISO-8601字符串转换为日期

Question

在Google Sheets单元格中将ISO-8601字符串转换为日期

datetimegoogle-sheetsgoogle-sheets-formulaiso8601

113

我有一列ISO-8601格式的字符串，想让谷歌表格将其视为日期，这样我就可以对它们进行数学计算（例如两个单元格之间的分钟差）。我尝试了=Date("2015-05-27T01:15:00.000Z")，但是无法实现。一定有简单的方法可以做到这一点。是否有建议？

- Bob Kuhar

14

令人惊讶的是，Google不支持正式的ISO-8601日期格式，这是唯一一个不支持的日期格式。 - Jefferey Cave

6个回答

41

我发现使用=SUM(SPLIT(A2,"TZ"))更加简单。

要将格式为yyyy-MM-dd HH:mm:ss.000的日期值再次显示为ISO-8601，请执行以下操作。

- Chris Bandy

2

这个解决方案还不错。它按照要求返回一个数字日期（时间）值，而不是像被接受的答案一样返回一个字符串。它最适合使用GMT时间戳，而不是带有时区偏移量的时间戳。如果给定了偏移量，此解决方案将使用“0”时间。这个解决方案聪明而简短，虽然不如其他答案正确，但比许多字符串函数调用更好。 - Mr. Lance E Sloan

这也可以正确处理毫秒。 - Nolan Amy

16

尝试这个

=CONCATENATE(TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),1),"yyyy-mm-dd")," ",TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),2),"hh:mm:ss"))

其中A1可以是一个包含ISO-8601格式字符串的单元格或字符串本身。

- Akshin Jalilov

1

嗯，这看起来并不“轻松”，但我会试一试。我真的希望已经有一个函数可以直接完成它。 - Bob Kuhar

这个解决方案返回一个字符串，而不是@BobKuhar想要的Date类型。当我使用ISO 8601时间字符串进行测试时，如2016-07-26T11:12:06.711-0400，结果为2016-07-26 11:12:06.711-0400。作为GMT时间字符串（没有时区偏移量），2016-07-26T11:12:06.711Z变成了2016-07-26 11:12:07。这个解决方案只是重新格式化ISO 8601字符串。 - Mr. Lance E Sloan

你需要将结果拆分，以便将其分解为“2016-07-26”和“11:12:07”。这两个单元格将被格式化为日期和时间。 - Akshin Jalilov

3

打开Apps Script并将此自定义函数粘贴到编辑器中。具体内容可参考Sheets函数指南。

function isoDate(isoDateString) {
  return new Date(isoDateString)
}

可以像以下公式一样使用：

=isoDate("2023-01-06T22:37:12Z")

结果将以日期形式显示在Sheets中。

- Lloyd

0

我发现使用正则表达式替换是一种更简单、干净、易读的方法：

=regexreplace(A1, "T|Z", " ") + B1

	A	B	C	D
1	2023-04-17T16:29:20.085Z	-3:00:00	1080805:29:20	4/17/2023 13:29:20

A1是要格式化的值；
B1是您所在地区的时区差异；
C1有公式而无格式；
D1具有相同的公式，格式为 日期时间。

正则表达式将字母T和Z替换为空格。

+ B1 部分将添加时间差以获取您所在时区的最终值。

如果您愿意，可以在公式中直接编写代码而不使用单元格。

=regexreplace(A1, "T|Z", " ") + "-3:00"

或者，如果您不希望在公式中出现这种差异，可以删除+ B1部分

=regexreplace(A1, "T|Z", " ")

- Alan Rezende

-1

我使用以下的Apps Script函数将ISO8601时间戳转换为相应电子表格时区的序列日期时间：

/** JS Date to Excel DateTime (AKA SERIAL_NUMBER date).
 *
 * @param date A JavaScript date object, possibly representing a datetime like 2022-11-11T15:24:00.000Z..
 * @param timeZoneOffsetMillies The time zone offset of the target serial date time (usually the one of the sheet).
 *
 * @return A Excel serial date like 44876.641666666605.
 */
const dateToSerialDateTime = function(date, timeZoneOffsetMillies) {
    /** Milliseconds per day (24 * 60 * 60 * 1000). */
    const MILLISECONDS_PER_DAY = 86400000;
    const timeZoneOffsetDays = timeZoneOffsetMillies / MILLISECONDS_PER_DAY;
    return ((date.getTime() / MILLISECONDS_PER_DAY) + 25569) + timeZoneOffsetDays; // 1970-01-01 - 1900-01-01 = 25569
};


/** Get the the timezone offset in milliseconds.
 *
 * @param timeZone The time zone in text format, ie. "Europe/Paris"
 * @return {number} Time zone offset in milliseconds.
 */
const getTimeZoneOffset = function(timeZone) {
    const strOffset = Utilities.formatDate(new Date(), timeZone, "Z");
    const offsetSeconds = ((+(strOffset.substring(0, 3))) * 3600) + ((+strOffset.substring(3)) * 60);
    return offsetSeconds * 1000;
}


/**
 * Returns the current sheets timezone.
 *
 * @return The current sheets timezone in IANA time zone database name format (ie Europe/Berlin).
 * @customfunction
 */
function TIMEZONE() {
    return SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
}


/**
 * Returns the current sheets timezone offset in milliseconds.
 *
 * @return The current sheets timezone offset in milliseconds.
 * @customfunction
 */
function TIMEZONE_OFFSET() {

    const tz = TIMEZONE();
    return tz != null ? getTimeZoneOffset(tz) : null;
}


/**
 * Convert ISO8601 timestamp strings (ie. 2022-11-22T14:47:01+0100) to a Sheets serial datetime.
 *
 * @param {string|Array<Array<string>>} input Input ISO8601 date string to parse.
 *
 * @return {number} The native sheets "serial datetime" as double (format the field as Number->Date Time manually).
 * @customfunction
 */
function PARSE_ISO8601(input) {

    const tzOffsetMillies = TIMEZONE_OFFSET();
    const parseIso8601 = ts => ts ? dateToSerialDateTime(new Date(ts), tzOffsetMillies) : null;

    return Array.isArray(input) ? input.map(row => row.map(field => parseIso8601(field))) : parseIso8601(input);
}

- Jonas Zeiger

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- rcoup · Accepted Answer

要获取可以使用普通数字格式化的实际日期值...

=DATEVALUE(MID(A1,1,10)) + TIMEVALUE(MID(A1,12,8))

例如:

	A	B
1	2016-02-22T05:03:21Z	2/22/16 5:03:21 AM

假设时间戳在UTC中
忽略毫秒(但您可以轻松添加)

DATEVALUE()函数将格式化的日期字符串转换为值，而TIMEVALUE()则对时间进行相同的操作。在大多数电子表格中，日期和时间由一个数字表示，其中整数部分是自1900年1月1日以来的天数，小数部分是时间作为一天的一部分的分数。例如，2009年6月11日17:30左右是约39975.72917。

上述公式分别解析日期部分和时间部分，然后将它们相加。