Excel中如何将带单位的时长字符串转换为HH:MM格式？

通用公式方案（兼容多数Excel版本） #

假设目标时长字符串在单元格A1，使用以下公式可直接转换为累计的HH:MM格式（忽略秒数，年按365天、月按30天换算）：

=TEXT(
    (IFERROR(FILTERXML("<t><v>"&SUBSTITUTE(SUBSTITUTE(A1,"""","")," ","</v><v>")&"</v></t>","//v[contains(.,'y')]")*365*24,0)+
    IFERROR(FILTERXML("<t><v>"&SUBSTITUTE(SUBSTITUTE(A1,"""","")," ","</v><v>")&"</v></t>","//v[contains(.,'mos')]")*30*24,0)+
    IFERROR(FILTERXML("<t><v>"&SUBSTITUTE(SUBSTITUTE(A1,"""","")," ","</v><v>")&"</v></t>","//v[contains(.,'w')]")*7*24,0)+
    IFERROR(FILTERXML("<t><v>"&SUBSTITUTE(SUBSTITUTE(A1,"""","")," ","</v><v>")&"</v></t>","//v[contains(.,'d')]")*24,0)+
    IFERROR(FILTERXML("<t><v>"&SUBSTITUTE(SUBSTITUTE(A1,"""","")," ","</v><v>")&"</v></t>","//v[contains(.,'h')]"),0)
    )/24+
    IFERROR(FILTERXML("<t><v>"&SUBSTITUTE(SUBSTITUTE(A1,"""","")," ","</v><v>")&"</v></t>","//v[contains(.,'m')]")/(24*60),0),
    "[hh]:mm"
)

逻辑说明：

1. 先移除字符串中的双引号，再用空格拆分每个时长片段为XML节点；
2. 通过FILTERXML提取各单位对应的数值，按换算规则转为小时数；
3. 将总小时和分钟数转为Excel日期格式，最后用TEXT函数格式化为[hh]:mm（支持超过24小时的累计显示）。

简化方案（仅适用于Excel 365及以后版本） #

利用TEXTSPLIT、LET、MAP函数实现更清晰的逻辑：

=LET(
    str, SUBSTITUTE(A1,"""",""),
    parts, TEXTSPLIT(str," "),
    total_hours, SUM(MAP(parts, LAMBDA(part,
        val, LEFT(part,LEN(part)-IF(RIGHT(part,3)="mos",3,1)),
        unit, RIGHT(part,IF(RIGHT(part,3)="mos",3,1)),
        SWITCH(unit,"y",val*365*24,"mos",val*30*24,"w",val*7*24,"d",val*24,"h",val,"m",0,"s",0,0)
    ))),
    total_mins, SUM(MAP(parts, LAMBDA(part, IF(RIGHT(part,1)="m",LEFT(part,LEN(part)-1),0)))),
    TEXT(total_hours/24 + total_mins/(24*60), "[hh]:mm")
)

补充说明 #

• 年、月的天数为近似值（1年=365天，1月=30天），若需更精准换算可自行调整对应系数；
• 你之前的公式报错原因：存在语法错误（多余的方括号、无效参数），且未处理不同单位的缺失情况（如部分字符串无年、月）。

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