想通过excel计算员工在特殊工作时间的加班时间

deko503

假设工作时间为上午8：00-12：00和下午13：30-17：00。由于员工工作均需要出车在外，时间非常不稳定，故需要在特定的工作时间之外计算员工的加班时间（即除工作时间以外所有时间均为加班）。想要用excel公式计算出如下效果：

日期   出车  返车  上班时间  加班时间
9-1     8:00   13:00       5       1
9-2     7:30   14:00       6.5     2.5
..........
9-29   12:00  16:00       4       1.5
9-30   15:00   20:00      5       3
合计                   xx       xx   

之前用=(B2<TIME(8,,))*(TIME(8,,)-B2)*24来计算加班时间，但涉及午休的时间就发现太复杂，不知道该怎样计算，其他函数也不太懂，跪求高手指点指点~~~

shuyee

是比较复杂.用了辅助列(如果不嫌烦,也能用公式表达的),在K列输入时间,本贴后部内容.
=ROUND(TEXT(TIME(,TEXT(INDEX(FREQUENCY($K$1:$K$10,B2:C2),2)*10-10,"0;\0;0"),)+TEXT($B$7-B2,"h:m:s;\0;\0")+TEXT(C2-$E$7,"h:m:s;\0;\0"),"[m]")/60,1)
解释一下:
1.用frequency,求中午休息时间,算法用点不可靠,有10分钟误差.
2.用frequency求得的是个3维数组,用index取出中间值,就是出车时间段在中午休息时间的投影.
由于求得的是个数,乘以间隔10分钟,即为中午加班时间(分钟数),注意,-10,是考虑边界问题,也是第1点不可靠的地方.
同时考虑到中午0加班,再-10,导致time函数出错,因此用text强制负数时为0.
3.用text格式函数,把第2步求得的分钟数,转化为小时数
4.求上班前加班小时数.
5.求下班后加班小时数.并与第3和4步求和,即为总加班小时数
6.再用text函数,转化为分钟数.注意[m]的应用
7.用分钟数/60,即为小数型的小时数

12:01
12:10
12:20
12:30
12:40
12:50
13:00
13:10
13:20
13:29

deko503

shuyee 发表于 2012-10-18 13:37
是比较复杂.用了辅助列(如果不嫌烦,也能用公式表达的),在K列输入时间,本贴后部内容.
=ROUND(TEXT(TIME(,TE ...

谢谢你的回复，本人第一次用excel做这种计算，您以上所说的我基本看不懂……
等待回复的时间我恶补了一下最基本的公式，用if貌似成功计算出加班的时间，虽然真心比较麻烦，但目测计算结果基本准确，您帮忙看一下看看有没需要调整的？ Book1.rar (7.1 KB, 下载次数: 51)

2012-10-18 16:26 上传

点击文件名下载附件
计算加班时间

shuyee

{:soso_e179:}当然正确.加油

shuyee

事实上2楼的公式是错的.抱歉{:soso_e127:}
下面这个是正确的.
第1个text,计算早上加班,第2个计算晚上加班,第3个计算中午加班,稍复杂些,要与中午上班班时间比较
=ROUND((TEXT(B2-"8:0:0","\0;[m];0")+TEXT(C2-"17:0:0","[m];\0;0")+TEXT(MIN(C2,"13:30:0")-MAX(B2,"12:0:0"),"[m];\0;0"))/60,1)

hisunny0213

学习，谢谢分享