B2=TEXT((IFERROR(-LOOKUP(1,-MID(A2,FIND("小",A2)-{1,2},{1,2})),)*3600+IFERROR(-LOOKUP(1,-MID(A2,FIND("分",A2)-{1,2},{1,2})),)*60+IFERROR(-LOOKUP(1,-MID(A2,FIND("秒",A2)-{1,2},{1,2})),))/86400,"[hh]:mm:ss")
另解{=TEXT(SUM(IFERROR(--RIGHTB(LEFT(A2,IFERROR(FIND({"小时","分钟","秒"},A2),1)-1),2),)*60^{2,1,0})/86400,"[hh]:mm:ss")
D2=TEXT(SUM(MMULT(MIDB(0&A2:A19&"00000",FINDB({"小","分","秒"},0&A2:A19&"0小分秒")-2,2)/{1,60,3600}/24,{1;1;1})),"[H]:M:S")
留言
張貼留言