加班时长扣除午休晚餐时间计算

micch

午餐晚餐时间固定已知，加班时间需要扣除吃饭时间。
以前做过很多次了，今年公式练习的少，都忘了。今天遇到类似的问题，公式都不会写了，用代码计算的。
发个贴，有闲的老师们，练练手，我再学习学习。

丢丢表格

法法条 发表于 2020-12-25 20:22
理解不了噢，能不能详细说说过程喔

很多公式都有浮点计算问题

=INT(TEXT((D2>=1)*43+SUM(TEXT(1%+MOD(C2:D2,1)*48-{0;23;26;34;36},"[<]!0")*{-1,1}*{1;-1;1;-1;1}),"[<2]!0"))/2
没辙  还是走套路吧。

丢丢表格

法法条 发表于 2020-12-25 20:40
大致讲讲思路吧，也让我学习吧丢老师

=TEXT(C2-{0;11.5;13;17;18}/24,"[<]!0)*{1;-1;1;-1;1}


就是计算 C2 的时间 在 0 ~ 11.5点 中有几个小时
                                 在 13~17 点钟有几个小时
                                 在 18 点后 有几个小时

micch

更新一下附件，一楼的模拟结果有错误，重新模拟了结果，想到迷糊，用林老师的公式和模拟结果验证，两者能对应上
容我继续研究，更新于2020平安夜

加班时长计算.zip (48.53 KB, 下载次数: 12)

2020-12-25 00:07 上传

点击文件名下载附件

补充：



附件： 加班时长计算(修正).zip (54.51 KB, 下载次数: 28)

2020-12-26 11:24 上传

点击文件名下载附件

法法条

M老师都不会的，我竟然也够胆进来看看，飘了

micch

法法条 发表于 2020-12-22 21:17
M老师都不会的，我竟然也够胆进来看看，飘了

我水平已经掉到幼稚园水平了。

用一堆if写的，以前做丢丢老师的题，还有其他很多次类似的问题，也忘了当初怎么做的了

正在写if呢，好歹自己先搞定

hbalyz

=FLOOR((C2-B2+MEDIAN(B2,$L$2:$M$2)-MEDIAN(C2,$L$2:$M$2)+MEDIAN(B2,$L$3:$M$3)-MEDIAN(C2,$L$3:$M$3))*24,0.5)

法法条

micch 发表于 2020-12-22 21:20
我水平已经掉到幼稚园水平了。

用一堆if写的，以前做丢丢老师的题，还有其他很多次类似的问题，也忘了 ...

M老师最多隔一天对吧，比如20：00到02:00这种，不会出现隔2天吧，比如今天20:00到后天早上2:00这样。

hbalyz

那半个小时是怎么算，是累加半小时，还是每个段半个小时
公式那个表我的是这样：=FLOOR((D2-C2+MEDIAN(C2,$K$2:$L$2)-MEDIAN(D2,$K$2:$L$2)+MEDIAN(C2,$K$3:$L$3)-MEDIAN(D2,$K$3:$L$3))*24,0.5)

micch

法法条 发表于 2020-12-22 21:24
M老师最多隔一天对吧，比如20：00到02:00这种，不会出现隔2天吧，比如今天20:00到后天早上2:00这样。

不会加超过24小时，最多隔一天。

我遇到的问题比这复杂，不过模拟数据，在sheet1里，应该最多是加不到24小时

micch

hbalyz 发表于 2020-12-22 21:35
那半个小时是怎么算，是累加半小时，还是每个段半个小时
公式那个表我的是这样：=FLOOR((D2-C2+MEDIAN(C2, ...

公式我研究一下，现在脑子不够用。

半小时的意思是加班时长计算完后，一小时以内都不算加班。超过一小时后按半小时累计，超过半小时而不足一小时只算半小时。举例就是结束时间-开始时间=8.8，这个总时间如果包含吃饭的时间就减去吃饭时间，得到的最终加班时间，再按半小时取整得出结果

补充：
median在这用的很好，研究了半天才搞懂。公式再加个不足一小时的不算加班就全功了。

gaozengxun

hbalyz 发表于 2020-12-22 21:35
那半个小时是怎么算，是累加半小时，还是每个段半个小时
公式那个表我的是这样：=FLOOR((D2-C2+MEDIAN(C2, ...

你的这公式怎么理解能解释一下吗