R3:R15=ROUND(B3/240*LEFT(Q3,FIND("'",Q3)-1)*1.34+B3/240/60*-LOOKUP(1,-MID(Q3,FIND("'",Q3)+1,{1,2}))*1.34,)
Q16 {=TEXT(SUM(IFERROR(SUBSTITUTE(LEFT(Q3:Q15,LEN(Q3:Q15)-1),"'",":")*1,)),"[H]'MM")&""""
另解 R3=IFERROR(ROUND(SUM(B3/240/{1,60}*MOD(TEXT(SUBSTITUTE(LEFT(Q3,LEN(Q3)-1),"'",":")*1,{"[H]","HMM"}),{9999,100})*1.34),),)
留言
張貼留言