求助贴，关于时间的计算

堆这么多，看都懒得看。时间函数无非就是转换一下格式照样加减，其实可以从源头优化记录的时间方式，然后列出穷举案例为休息或上班等状态，再引用，把源表格式排版重新设定一下可以方便后续作业。

为什么这么填写？？就这一条数据还是有很多？？？

设置模板 ，开始时间，截止时间，时间类别（休息，用餐），这样一下子就出来结果了，非要整的表格人不人鬼不鬼的？

=SUM(IF(MMULT(N(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))>=TRANSPOSE(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))),ISODD(MATCH(SMALL(1*SUBSTITUTE(MID(TEXTJOIN(",",,A2,B2),(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))*6-5,5),IF(ISEVEN(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),"00:00",""),"24:00"),ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),1*SUBSTITUTE(MID(TEXTJOIN(",",,A2,B2),(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))*6-5,5),IF(ISEVEN(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),"00:00",""),"24:00"),))*2-1)=1,IF(ISODD(MATCH(SMALL(1*SUBSTITUTE(MID(TEXTJOIN(",",,A2,B2),(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))*6-5,5),IF(ISEVEN(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),"00:00",""),"24:00"),ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),1*SUBSTITUTE(MID(TEXTJOIN(",",,A2,B2),(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))*6-5,5),IF(ISEVEN(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),"00:00",""),"24:00"),))*2-1=1,-1,),IF(MMULT(N(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))>=TRANSPOSE(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))),ISODD(MATCH(SMALL(1*SUBSTITUTE(MID(TEXTJOIN(",",,A2,B2),(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))*6-5,5),IF(ISEVEN(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),"00:00",""),"24:00"),ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),1*SUBSTITUTE(MID(TEXTJOIN(",",,A2,B2),(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))*6-5,5),IF(ISEVEN(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),"00:00",""),"24:00"),))*2-1)=0,1,))*SMALL(1*SUBSTITUTE(MID(TEXTJOIN(",",,A2,B2),(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2)))*6-5,5),IF(ISEVEN(ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))),"00:00",""),"24:00"),ROW(OFFSET(A1,,,(LEN(TEXTJOIN(",",,A2,B2))-LEN(SUBSTITUTE(TEXTJOIN(",",,A2,B2),",",))+1)*2))))*24

=SUM(BYROW(WRAPROWS(REGEXP(A2,"..:.."),2),LAMBDA(x,MOD(SUM(x*{-1,1}),1)*24)))+NPV(-2,REGEXP(B2,"..:..")*24)-0.5

=LET(s,--TEXTSPLIT(A2,"-",","),r,TEXTSPLIT(B2,,"-")*1440,h,MAX(r)-@r+1,SUM(IF(s,s,1)*{-24,24},h/60,-BYCOL(VDB(,,DROP(s,,1),TAKE(s,,1),SEQUENCE(,h,@r)/1440),COUNT)/60))
来自熊猫佬

=LET(f,LAMBDA(z,LET(t,TEXTSPLIT(z,"-",","),(SEQUENCE(ROWS(t),2)>=IFNA(MATCH(1=2,MMULT(-t,{1;-1})>0,),9)*2)+t)),x,VSTACK(f(A2),f(B2)),(SUM(N(BYCOL(VDB(,,DROP(x,,1),TAKE(x,,1)+1%%,COLUMN(A:DFS)/1440),COUNT)>0)))/60)

不要再去堆屎山了，老老实实加点辅助列不好吗

=LET(p,TEXT(TEXTSPLIT(A2&","&B2,"-",",")+0.5,"h:m")*1440,SUM(N(MAP(ROW(1:1440),LAMBDA(x,OR((x<INDEX(p,,2))*(x>=INDEX(p,,1)))))))/60)

什么样的脑子才能堆那么多函数在里面啊/佩服

旧版本能用的，没啥意思
=SUM(N(MMULT((ROW(1:1440)<IFERROR(TEXT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2&","&B2,",",REPT(" ",98)),"-",REPT(" ",98)),COLUMN(A:I)*198-99,99))+0.5,"h:m")*1440,))*(ROW(1:1440)>=IFERROR(TEXT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2&","&B2,",",REPT(" ",98)),"-",REPT(" ",98)),COLUMN(A:I)*198-197,99))+0.5,"h:m")*1440,)),ROW(1:9)^0)>0))/60