格式有点乱，计算时长

天涯论剑

停留时间的格式有点乱，帮忙计算出场时间减去停留时间，得出入场时间是多少（yyyy-mm-dd hh:mm:ss格式）

hugeinfo

=A2+SUMPRODUCT(IFERROR(MIDB(0&B2,FINDB({"小","分","秒"},0&B2)-2,2),)/{1,60,3600}*60/24/60)

天涯论剑

hugeinfo 发表于 2024-8-26 17:17
=A2+SUMPRODUCT(IFERROR(MIDB(0&B2,FINDB({"小","分","秒"},0&B2)-2,2),)/{1,60,3600}*60/24/60)

老哥，天没有计算进去

无关岁月

wps需要改成REGEXP

1. =A2-SUM(IFNA(REGEXEXTRACT(B2,"\d+(?="&{"天","小时","分钟","秒"}&")")/{1,24,1440,86400},))

复制代码

无关岁月

公式要审&核，先上个图吧

小凡、、、

=A2-LET(a,SUBSTITUTES(B2,{"天","小时","分钟","秒"},{"*1+","/24+","/24/60+","/24/60/60+"}),EVALUATE(LEFT(a,LEN(a)-1)))
wps试试这样

博爱风雨同舟

=LET(A,TEXT(SUM(IFERROR(MIDB(0&B2,FINDB({"天","小","分","秒"},0&B2)-2,2),)/{1,1,60,3600}*{24,1,1,1})/24,"D-H-M-S"),(A2*24+LEFT(A,FIND("-",A)-1)*24+SUBSTITUTE(MID(A,FIND("-",A)+1,9),"-",":")*24)/24)

小凡、、、

=A2-LET(a,SUBSTITUTES(B2,{"天","小时","分钟","秒"},{"*1+","/24+","/24/60+","/24/60/60+"}),EVALUATE(LEFT(a,LEN(a)-1)))
卡审核了

hugeinfo

天涯论剑 发表于 2024-8-26 17:20
老哥，天没有计算进去

=A2+IFERROR(MID(B2,1,FIND("天",B2)-1),0)+SUMPRODUCT(IFERROR(MIDB(0&B2,FINDB({"小","分","秒"},0&B2)-2,2),)/{1,60,3600}*60/24/60)