计算出勤小时的公式:“=IF(AND(A2>=TIME(7,15,0),A2<=TIME(7,40,0),B2>=TIME(11,25,0),B2<=TIME(11,40,0)),4,ROUNDUP((B2-A2)*48,0)*0.5)”
1、在excel2016中,建立表格。
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/d62a6059252dd42ae2464f1f0e3b5bb5c9eab833?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
2、在c2中输入公式,可以看见,7:40—11:25,算作4小时。
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/d1160924ab18972b8ce24e7eebcd7b899f510aad?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
3、填充后的计算结果。
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/aec379310a55b319994fcbc14ea98226cefc17cb?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
4、增加6:13—12:00,取整数6。
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/e61190ef76c6a7efcc4de092f0faaf51f3de661e?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
5、“,ROUNDUP((B2-A2)*48,0)*0.5”中的48,因为结果是以天为单位了,想让结果为小时就必须乘24,是强制转为时间格式。在e2输入“=b2-a2",得到”0.16“。
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/9825bc315c6034a8503a0c35c6134954082376ad?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)