去掉休息时间统计上班时间

旻-风

考勤中去掉休息时间计算上班小时数据
说明：
1、原始数据包含上班时间和下班时间。所有时间有跨天的，但跨天的下班时间在当天8:30之前，不会造成混乱；
2、上班时间按照后面半个小时整数计算，例如，8:21按照8:30开始计算，8:30按照8:30计算，订单8:31则按照9:00开始计算，以此类推；同理，下班时间按照之前的半小时整数计算，例如，11:20按照11:00计算，11:30按照11:30计算，11:40也按照11:30计算；
3、中午12:00至13:00,17:30至18:00位休息时间，如果在休息时间上班的，以休息结束的时间计算上班，例如12:20上班的，按照13:00上班，同理，12:59下班的，按照12:00下班，18:30下班的，按照17:30下班；计算上班时长需要去掉休息时间，其余时间段无休息时间；
4、数据很多，例举了部分，需要按照人数统计上班时长（按照小时计数）；
5、大概需要用函数解决的问题：
a、如何判断按照整半小时统计上下班时间，例如上班8:21按照8:30开始计算，下班11:40也按照11:30计算；
b、如何判定上下班时间中是否包含休息时间；
c、如何休息时间内的上下班时间计算，例如12:20上班的，按照13:00上班，18:30下班的，按照17:30下班；
d、如何计算跨天时间；
e、其他问题

约定的童话

估计要石沉大海了，太繁琐了.....

hcm19522

=(SUM(FREQUENCY(ROW(INDIRECT(ROUNDUP(D2*24/0.5,)&":"&INT(E2*24/0.5)+(D2>E2)*48)),{24,26,35,36})*{1;0;1;0;1})-1)/2

micch

1. =MOD(FLOOR(MIN(IF((E2>{12,17.5}/24)*(E2<{13,18}/24),{12,17.5}/24,E2)),"0:30")-CEILING(MAX(IF((D2>{12,17.5}/24)*(D2<{13,18}/24),{13,18}/24,D2)),"0:30"),1)-SUM(IF((D2<{12;17.5}/24)*(E2>{13;18}/24),{1;0.5}/24))

复制代码

不会简单的了，只好if然后两个时间相减了；下班时间-上班时间-休息时间

旻-风

hcm19522 发表于 2018-10-7 17:44
=(SUM(FREQUENCY(ROW(INDIRECT(ROUNDUP(D2*24/0.5,)&":"&INT(E2*24/0.5)+(D2>E2)*48)),{24,26,35,36})*{1;0 ...

这个结果有一点错误，上班时间为休息时间的上班小时数不对，不过还是表示感谢。为了使公式简单，我将上下班时间全部改成了半点整，同时，将少数休息时间上班也改成标准时间了，跨天的也改成两行当天的了，仅仅需要去掉休息时间了，如果上班时间包含12:00到13：00就减去1小时，如果包含17:30到18:00，就减去0.5小时，豆包含则减去1.5小时，是否有简单公式？谢谢！

旻-风

micch 发表于 2018-10-7 18:36
不会简单的了，只好if然后两个时间相减了；下班时间-上班时间-休息时间

感谢，完全正确。另外，为了使公式简单，我将上下班时间全部改成了半点整，同时，将少数休息时间上班也改成标准时间了，跨天的也改成两行当天的了，也就是说，不用将上下班时间改成整点或者半点整，也不需要考虑休息时间上班调整，也不需要考虑跨天，仅仅需要去掉休息时间了，如果上班时间包含12:00到13：00就减去1小时，如果包含17:30到18:00，就减去0.5小时，豆包含则减去1.5小时，是否有简单公式？谢谢！

micch

SUM(IF((D2<{12;17.5}/24)*(E2>{13;18}/24),{1;0.5}/24))

这一段就是计算休息时间的，你的意思就是下班时间直接减上班时间，然后去掉休息时间？

丢丢表格

micch 发表于 2018-10-7 18:36
不会简单的了，只好if然后两个时间相减了；下班时间-上班时间-休息时间

可以参考 http://club.excelhome.net/thread-1414257-1-1.html
  他的问题比竞赛题更简单 。
要是用逐分累积法：
=SUM(LOOKUP(MOD(TEXT((CEILING(D2*1440,30)+ROW($1:$1440)-1+1%%)/60,"[>"&FLOOR((E2+(E2<D2))*1440,30)/60&"]!12"),24),{0,12,13,17.5,18;1,0,1,0,1}))/60

micch

丢丢表格 发表于 2018-10-7 20:45
可以参考 http://club.excelhome.net/thread-1414257-1-1.html
  他的问题比竞赛题更简单 。

呃，这个竞赛题费尽了脑子，不会套用那个思路-_-||

旻-风

micch 发表于 2018-10-7 20:40
SUM(IF((D2{13;18}/24),{1;0.5}/24))

这一段就是计算休息时间的，你的意思就是下班时间直接减上班时间， ...

是的，本来很复杂的，把原始数据调简单了