提问人：Aivar 提问时间：11/1/2023 最后编辑：Mayukh BhattacharyaAivar 更新时间：11/2/2023 访问量：38
Google 表格的 ISO 持续时间格式
ISO Duration format to google sheets
问：
请告诉我如何在谷歌表格中将 ISO 8601 持续时间 （P2Y3M4DT1H2M1S） 格式转换为小时数，前提是某些字母可能不是（P1D、PT2H）等。也许谷歌表格中有一个公式？先谢谢你
通过chatgpt尝试但没有成功
答：
0赞
FBrTeach
11/2/2023
#1
我相信我有解决方案，尽管目前尚不清楚您确切想要哪种格式。
=LET(stamp,REGEXEXTRACT(A1,"(-)?P(?:([.,\d]+)Y)?(?:([.,\d]+)M)?(?:([.,\d]+)W)?(?:([.,\d]+)D)?(?:T(?:([.,\d]+)H)?(?:([.,\d]+)M)?(?:([.,\d]+)S)?)?"),
IFNA(LET(year, INDEX(stamp,2),
month,INDEX(stamp,3),
days,INDEX(stamp,5),
hours,INDEX(stamp,6),
minutes,INDEX(stamp,7),
seconds,INDEX(stamp,8),
LET(total_seconds,days*24*60*60+hours*60*60+minutes*60+seconds,
LET(format_seconds,MOD(total_seconds,60),
LET(total_minutes,QUOTIENT(total_seconds-format_seconds,60),
LET(format_minutes,MOD(total_minutes,60),format_hours,QUOTIENT(total_minutes,60),
format_hours&":"&format_minutes&":"&format_seconds))))),))
经过退货测试P3Y6M4DT12H30M5S108:30:5
将其分解为几个部分：
IFNA(..., )
这意味着如果它与正则表达式不匹配，则返回一个空白单元格。
REGEXEXTRACT(A1,"(-)?P(?:([.,\d]+)Y)?(?:([.,\d]+)M)?(?:([.,\d]+)W)?(?:([.,\d]+)D)?(?:T(?:([.,\d]+)H)?(?:([.,\d]+)M)?(?:([.,\d]+)S)?)?")
将所有相关数字提取到一个数组中。正则表达式取自此处。
LET(year, INDEX(stamp,2),
month,INDEX(stamp,3),
days,INDEX(stamp,5),
hours,INDEX(stamp,6),
minutes,INDEX(stamp,7),
seconds,INDEX(stamp,8),
提取这些特定位置的数字。
请注意，年份和月份不会在以后使用，因为您无法判断它是哪个月份或年份才准确。
LET(total_seconds,days*24*60*60+hours*60*60+minutes*60+seconds
这只会将所有相关值转换为秒。如果您想做一些其他格式，从秒开始可以让您更轻松地更改它。
LET(format_seconds,MOD(total_seconds,60),
LET(total_minutes,QUOTIENT(total_seconds-format_seconds,60),
LET(format_minutes,MOD(total_minutes,60),format_hours,QUOTIENT(total_minutes,60),
format_hours&":"&format_minutes&":"&format_seconds
这会将秒转换为允许小时数超过 24 小时的格式，并且还允许使用十进制秒。HH:MM:SS
可以同时适用于整个范围的版本如下：
=MAP(A1:A6,LAMBDA(full_stamp,LET(stamp,REGEXEXTRACT(full_stamp,"(-)?P(?:([.,\d]+)Y)?(?:([.,\d]+)M)?(?:([.,\d]+)W)?(?:([.,\d]+)D)?(?:T(?:([.,\d]+)H)?(?:([.,\d]+)M)?(?:([.,\d]+)S)?)?"),
IFNA(LET(year, INDEX(stamp,2),
month,INDEX(stamp,3),
days,INDEX(stamp,5),
hours,INDEX(stamp,6),
minutes,INDEX(stamp,7),
seconds,INDEX(stamp,8),
LET(total_seconds,days*24*60*60+hours*60*60+minutes*60+seconds,
LET(format_seconds,MOD(total_seconds,60),
LET(total_minutes,QUOTIENT(total_seconds-format_seconds,60),
LET(format_minutes,MOD(total_minutes,60),format_hours,QUOTIENT(total_minutes,60),
format_hours&":"&format_minutes&":"&format_seconds
))))),))))
只需将 A1：A6 更改为相关范围即可。
评论