遇到同事的一个问题,求某段时间内除双休日、节假日以外的时间差,格式是[h]:mm(xx小时xx分)。这个要求是用在统计多少个工作日完成的延迟上,或者以此来统计及时率。
为此设计了一个表格如下图,设定了整个第二行2:2,存放国定假日。
A5:A8为条件区域,可能在某个时间段内会遇到的状况。
依据A列的条件,在B5:C8输入“起始时间”与“结束时间” 作为案例。
在D5:D8间人工统计除节假日外的时间差。(节假日包括双休日与国定假日)
004134692.jpg
然后在E5:E8依据A列条件单独计算。
004219674.jpg
此处主要利用了NETWORKDAYS函数,计算除节假日外的工作日有多少天。
语法:NETWORKDAYS(start_date, end_date, [holidays])
start_date:起始时间
end_date:结束时间
[holidays]:人工设定的除双休日外的假日,如每年的国庆节。若统计的时间段内无相关人为假日也可以省略。
需要统计的时间格式是[h]:mm(xx小时xx分),输入如下公式:
1、假期开始,工作日结束:=NETWORKDAYS(B5,C5,2:2)+(C5-INT(C5))-1
(1)NETWORKDAYS(B5,C5,2:2):2:2即第二行为国定假日区域,求得两时间之间工作日天数为1;
(2)(C5-INT(C5)):求得结束时间当天所用时间,得出的时间为10:00。
(3)-1:因为前面Networkdays函数求得天数为1个工作日,但当天仅用了10:00延迟。所以需要-1。
2、假期开始,假期结束:=0
3、工作日开始,工作日结束:=C7-B7+NETWORKDAYS(B7,C7,$2:$2)-2
4、工作日开始,假期结束:=1-(B8-INT(B8))+NETWORKDAYS(B8,C8,$2:$2)-2
单独的公式完成后,利用IF函数对几个条件进行嵌套,可以先直接引用E5到E8单元格,之后再填入这些条件公式。
最终F5中的公式为:
=IF(NETWORKDAYS(B5,B5,$2:$2)=0,IF(NETWORKDAYS(C5,C5,$2:$2)=0,0,NETWORKDAYS(B5,C5,2:2)+(C5-INT(C5))-1),IF(NETWORKDAYS(C5,C5,$2:$2)=0,1-(B5-INT(B5))+NETWORKDAYS(B5,C5,$2:$2)-2,C5-B5+NETWORKDAYS(B5,C5,$2:$2)-2))
004236250.jpg
拖拽填充公式至F5:F8区域,计算完成。
阅读(2278) | 评论(0) | 转发(0) |