请问各位EXCEL高手，请教一个关于函数的问题

ZhouFfett

长长的公式：
=INT(A5)+INT(B5/TIME(9,,))+(A5-INT(A5)-TIME(7,30,)-IF(A5-INT(A5)>TIME(12,,),TIME(0,30,),)+MOD(B5,TIME(9,,))>TIME(9,,))+MOD(A5-INT(A5)-TIME(7,30,)-IF(A5-INT(A5)>TIME(12,,),TIME(0,30,),)+MOD(B5,TIME(9,,)),TIME(9,,))+TIME(7,30,)+IF(MOD(A5-INT(A5)-TIME(7,30,)-IF(A5-INT(A5)>TIME(12,,),TIME(0,30,),)+MOD(B5,TIME(9,,)),TIME(9,,))>TIME(4,,),TIME(,30,),)

595050627

ZhouFfett 发表于 2021-5-8 16:35
长长的公式：
=INT(A5)+INT(B5/TIME(9,,))+(A5-INT(A5)-TIME(7,30,)-IF(A5-INT(A5)>TIME(12,,),TIME(0,30, ...

请问老师方便给我说一下意思吗？

ZhouFfett

595050627 发表于 2021-5-19 16:49
请问老师方便给我说一下意思吗？

用第四行的公式分析好些

=INT(A4)+INT(B4/TIME(9,,))+(E4+F4>TIME(9,,))+MOD(E4+F4,TIME(9,,))+TIME(7,30,)+IF(MOD(E4+F4,TIME(9,,))>TIME(4,,),TIME(,30,),)

其中，前半段计算天： INT(A4)+INT(B4/TIME(9,,))+(E4+F4>TIME(9,,))
      后半段计算时分秒 MOD(E4+F4,TIME(9,,))+TIME(7,30,)+IF(MOD(E4+F4,TIME(9,,))>TIME(4,,),TIME(,30,),)

INT(A4) 是从首日的零时开始计算

595050627

ZhouFfett 发表于 2021-5-20 08:43
用第四行的公式分析好些

=INT(A4)+INT(B4/TIME(9,,))+(E4+F4>TIME(9,,))+MOD(E4+F4,TIME(9,,))+TIME(7 ...

哦，谢谢老师了。

595050627

ZhouFfett 发表于 2021-5-8 16:35
长长的公式：
=INT(A5)+INT(B5/TIME(9,,))+(A5-INT(A5)-TIME(7,30,)-IF(A5-INT(A5)>TIME(12,,),TIME(0,30, ...

=INT(A5)+INT(B5/TIME(9,,))+(A5-INT(A5)-TIME(7,30,)-IF(A5-INT(A5)>TIME(12,,),TIME(0,30,),)+MOD(B5,TIME(9,,))>TIME(9,,))+MOD(A5-INT(A5)-TIME(7,30,)-IF(A5-INT(A5)>TIME(12,,),TIME(0,30,),)+MOD(B5,TIME(9,,)),TIME(9,,))+TIME(7,30,)+IF(MOD(A5-INT(A5)-TIME(7,30,)-IF(A5-INT(A5)>TIME(12,,),TIME(0,30,),)+MOD(B5,TIME(9,,)),TIME(9,,))>TIME(4,,),TIME(,30,),)
老师您好！上次您给我的函数公式很实用，现在我们的工作时间调整了，所以我还想向您请教一下。就是我们现在的工作时间更改为12个小时了，就是早上8点到晚上8点为工作时间，然后中午11点半到12点是吃饭时间，下午的五点半到六点是吃饭时间，请问一下这个函数公式应该怎么做啊，谢谢您了，老师，麻烦帮我一下