EXCEL 时间差问题: B2格 8:58:00 AM C2格 12:30:00 PM D2格 1:15...

发布网友发布时间：2024-09-30 01:19

共3个回答

热心网友时间：2024-10-04 07:12

首先把原工作时间变为分钟--再把分钟变为小时减去规定工作时间8小时余下的0.05小时为了便于理解又变为分钟显示。
=(((--TEXT(SUBSTITUTE(SUBSTITUTE(C2,"小时",":"),"分",":"),"[m]"))-(--TEXT(SUBSTITUTE(SUBSTITUTE(B2,"小时",":"),"分",":"),"[m]"))+(--TEXT(SUBSTITUTE(SUBSTITUTE(E2,"小时",":"),"分",":"),"[m]"))-(--TEXT(SUBSTITUTE(SUBSTITUTE(D2,"小时",":"),"分",":"),"[m]")))/60-8)*60
最后准确值是多出3分钟。

热心网友时间：2024-10-04 07:18

在F2中输入或复制粘贴此公式
=IF(E2-"9:00">=(D2-C2)+"8:00","多做"&TEXT(E2-"9:00"-(D2-C2)-"8:00","H时M分"),"少做"&TEXT((D2-C2)+"8:00"-(E2-"9:00"),"H时M分"))
下拉填充

热心网友时间：2024-10-04 07:17

F2中输入公式：
=TEXT(ABS(8/24-(C2-B2+E2-D2)),IF((C2-B2+E2-D2)*24>8,"多出hh:mm","少做hh:mm"))
下拉。