在 GoogleSheets 单元格中的 ISO-8601字符串到日期

小开

Try this

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

Where A1 can be a cell with ISO-8601 formatted string or the string itself.

小开

最佳答案

To get an actual Date value which you can format using normal number formatting...

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

eg.

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

Assumes timestamps are in UTC
Ignores milliseconds (though you could add easily enough)

The DATEVALUE() function turns a formatted date string into a value, and TIMEVALUE() does the same for times. In most spreadsheets dates & times are represented by a number where the integer part is days since 1 Jan 1900 and the decimal part is the time as a fraction of the day. For example, 11 June 2009 17:30 is about 39975.72917.

The above formula parses the date part and the time part separately, then adds them together.

小开

I found it much simpler to use =SUM(SPLIT(A2,"TZ"))

Format yyyy-MM-dd HH:mm:ss.000 to see the date value as ISO-8601 again.

小开

I use the following Apps Script functions to convert ISO8601 timestamps to serial datetimes in the respective spreadsheet's time zone:

/** 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);
}