(Owed by: 春夜喜雨 http://blog.csdn.net/chunyexiyu)
引言
计算任务完成时间周期，和计算金钱一样，是一个比较细致严谨的工作。
通常，我们可能以为，完成周期形如：
任务完成周期 = 任务结束时间 – 任务开始时间
但是一般情况下，对于正常社会企业，通常都是要把排除掉休息日来计算的，来计算的任务完成周期。
而要排除休息日就是一个比较复杂的事情了。
1. 识别工作日与休息日
首先节假日本身的规律性不是很强，每年都会不同，我们知道，节假日的计算要考虑几种情况
- 周末双休日；
- 因法定节假日调整的休息日(非周末)；
- 因法定节假日调整的工作日(周末)；
所以
一年中的休息日 = 周末双休日总和 + 因法定节假日调整的休息日(非周末) – 因法定节假日调整的工作日(周末)
一年中的工作日 = 一年的天数总数 – 一年中的休息日
Excel中工作日天数计算：
(注意计算粒度是天，相当于从任务开始日期的00:00:00，到任务结束日期的24:00:00)
([周末日]中填写的”0000000″，代表周一到周日都不算休息日，统一在[节假日]中指定休息日)
([节假日]中列出所有的休息日，包括所有法定的休息日：周末休息日，非周末调整的节假日，但不包括调整为工作日的周末)
NETWORKDAYS.INTL(任务开始日期, 任务结束日期, “0000000”, [节假日])
Excel中休息日天数计算：
NETWORKDAYS.INTL(任务开始日期，任务结束日期，“0000000”) – NETWORKDAYS.INTL(任务开始日期, 任务结束日期, “0000000”, [节假日])
样例公式：
NETWORKDAYS.INTL(J3,K3,“0000000”)-NETWORKDAYS.INTL(J3,K3,“0000000”,O1:O19)
2. 计算任务完成的工作日时间段
下面就以“休息日”代指所有的法定的休息日；以“工作日”代指除“休息日”之外的法定工作日。
在许多项目中，主要要求计算的工作日时间，出现的场景可能有几种情况：
场景1. 开始在某个工作日，结束在某个工作日；
场景2. 开始在某个工作日，结束在某个休息日；
场景3. 开始在某个休息日，结束在某个工作日；
场景4. 开始在某个休息日，结束在某个休息日；
另外还有一些特殊场景：
场景5. 开始日与结束日在同一天时，该天是工作日；
场景6. 开始日与结束日在同一天时，该天是休息日；
注：任务开始时间、结束时间是带了时、分、秒
分别演算这些场景的计算公式情况，从而合理化的计算工作时间：
注：1天时间 = 24小时 = 1440分钟 = 86400秒
场景1：开始在某个工作日，结束在某个工作日
任务完成周期 = 任务结束时间 – 任务开始时间 – (任务周期中的休息日天数 * 1天时间）
场景2：开始在某个工作日，结束在某个休息日
任务完成周期 = 任务结束时间(格式化到23:59:59) – 任务开始时间 – (任务周期中的休息日天数 * 1天时间）
(调整方式：TEXT(任务结束时间,“yyyy/m/d 23:59:59”)）
场景3. 开始在某个休息日，结束在某个工作日；
任务完成周期 = 任务结束时间- 任务开始时间(格式化到00:00:00) – (任务周期中的休息日天数 * 1天时间）
(调整方式：TEXT(任务开始时间,“yyyy/m/d 00:00:00”)）
场景4. 开始在某个休息日，结束在某个休息日；
任务完成周期 = 任务结束时间(格式化到23:59:59) – 任务开始时间(格式化到00:00:00) – (任务周期中的休息日天数 * 1天时间）
场景5. 开始日与结束日在同一天时，该天是工作日；
任务完成周期 = 任务结束时间 – 任务开始时间
也适用场景1公式：
任务完成周期 = 任务结束时间 – 任务开始时间 – (任务周期中的休息日天数 * 1天时间）
场景6. 开始日与结束日在同一天时，该天是休息日；
任务完成周期 =0
也适用场景4公式：
任务完成周期 = 任务结束时间(格式化到23:59:59) – 任务开始时间(格式化到00:00:00) – (任务周期中的休息日天数 * 1天时间）
综合所有的场景之后，得出的综合公式如下：
- 如果任务结束时间是休息日，任务结束时间调整到23:59:59，工作日时保持不变；
- 如果任务开始时间是休息日，任务开始时间调整到00:00:00，工作日时保持不变；
- 任务完成周期 = 调整后任务结束时间 – 调整后任务开始时间 – (任务周期中的休息日天数 * 1天时间）
3. 计算公式
- 获取调整后时间：
判定是否是休息日，可以使用TEXT(时间,“yyyy/m/d”)格式化日期，之后在休息日列表中IFERROR(MATCH(日期,[休息日]),0)查找
开始时间调整方式：TEXT(任务开始时间,“yyyy/m/d 00:00:00”)
结束时间调整方式：TEXT(任务结束时间,“yyyy/m/d 23:59:59”)
EXCEL中使用IF语句来选取不同的值：IF (IFERROR( MATCH(TEXT(日期时间,“yyyy/m/d”),[休息日]) ,0), TEXT(日期时间,“yyyy/m/d 23:59:59”), 日期时间)，获取到检查休息日调整后的时间
样例公式，
开始时间：IF(IFERROR(MATCH(TEXT(j3,“yyyy/m/d”),O1:O19,0),0),TEXT(j3,“yyyy/m/d 00:00:00”),j3)
结束时间： IF(IFERROR(MATCH(TEXT(k3,“yyyy/m/d”),O1:O19,0),0),TEXT(k3,“yyyy/m/d 23:59:59”),k3)
- Excel中休息日天数计算：
NETWORKDAYS.INTL(任务开始日期，任务结束日期，“0000000”) – NETWORKDAYS.INTL(任务开始日期, 任务结束日期, “0000000”, [节假日])
样例公式，
NETWORKDAYS.INTL(J3,K3,“0000000”)-NETWORKDAYS.INTL(J3,K3,“0000000”,O1:O19)
- 总的计算时间段样例公式(算出的x.x天) ：
任务结束时间(格式化到23:59:59) – 任务开始时间(格式化到00:00:00) – 任务周期中的休息日天数
IF(IFERROR(MATCH(TEXT(K3,“yyyy/m/d”),O1:O19,0),0),TEXT(K3,“yyyy/m/d 23:59:59”),K3)
-IF(IFERROR(MATCH(TEXT(J3,“yyyy/m/d”),O1:O19,0),0),TEXT(J3,“yyyy/m/d 00:00:00”),J3)
-(NETWORKDAYS.INTL(J3,K3,“0000000”)-NETWORKDAYS.INTL(J3,K3,“0000000”,O1:O19))
(Owed by: 春夜喜雨 http://blog.csdn.net/chunyexiyu)
原文地址:https://blog.csdn.net/chunyexiyu/article/details/135872098
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_62989.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
