如何在Google Sheets中将ISO 8601时长格式转换为小时？

可以通过结合正则表达式提取各时长分量，再转换为小时并求和的方式实现。以下是直接可用的公式，支持处理P2Y3M4DT1H2M1S、P1D、PT2H等各种缺失部分的ISO 8601时长格式：

完整公式（单行版） #

=SUM(IFERROR(REGEXEXTRACT(A1,"(\d+)Y")*365*24,0),IFERROR(REGEXEXTRACT(A1,"(\d+)M(?!T)")*30*24,0),IFERROR(REGEXEXTRACT(A1,"(\d+)D")*24,0),IFERROR(REGEXEXTRACT(A1,"T(\d+)H")*1,0),IFERROR(REGEXEXTRACT(A1,"T.*?(\d+)M")/60,0),IFERROR(REGEXEXTRACT(A1,"T.*?(\d+)S")/3600,0))

公式拆解说明 #

每个分量的转换逻辑如下：

• 年转小时：用REGEXEXTRACT(A1,"(\d+)Y")提取年数，按每年365天计算总小时数，缺失时返回0
• 月转小时：用(\d+)M(?!T)的负向断言确保提取的是日期部分的月份（而非时间部分的分钟），按每月30天估算总小时数，缺失时返回0
• 天转小时：提取天数后乘以24，缺失时返回0
• 小时直接取值：提取T之后的小时数，缺失时返回0
• 分钟转小时：提取T之后的分钟数，除以60转换为小时，缺失时返回0
• 秒转小时：提取T之后的秒数，除以3600转换为小时，缺失时返回0
• 求和：将所有转换后的小时数相加得到总时长

注意事项 #

• 月份按每月30天为估算值，若需更精确的转换（如结合具体日期计算实际月份天数），需额外补充逻辑，但ISO 8601相对时长本身无关联日期，通常使用估算值即可
• 将公式中的A1替换为你实际存放ISO 8601时长的单元格

内容的提问来源于stack exchange，提问作者Aivar