(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：开始在某个工作日，结束在某个休息日
任务完成周期 = 任务结束时间(格式化到24:00:00或23:59:59) - 任务开始时间 - (任务周期中的休息日天数 * 1天时间） (调整方式：TEXT(任务结束时间,“yyyy/m/d 24:00:00”)）
场景3. 开始在某个休息日，结束在某个工作日；
任务完成周期 = 任务结束时间- 任务开始时间(格式化到00:00:00) - (任务周期中的休息日天数 * 1天时间） (调整方式：TEXT(任务开始时间,“yyyy/m/d 00:00:00”)）
场景4. 开始在某个休息日，结束在某个休息日；
任务完成周期 = 任务结束时间(格式化到24:00:00或23:59:59) - 任务开始时间(格式化到00:00:00) - (任务周期中的休息日天数 * 1天时间）
场景5. 开始日与结束日在同一天时，该天是工作日；
任务完成周期 = 任务结束时间 - 任务开始时间 也适用场景1公式： 任务完成周期 = 任务结束时间 - 任务开始时间 - (任务周期中的休息日天数 * 1天时间）
场景6. 开始日与结束日在同一天时，该天是休息日；
任务完成周期 =0 也适用场景4公式： 任务完成周期 = 任务结束时间(格式化到24:00:00或23:59:59) - 任务开始时间(格式化到00:00:00) - (任务周期中的休息日天数 * 1天时间）
综合所有的场景之后，得出的综合公式如下：
如果任务结束时间是休息日，任务结束时间调整到24:00:00或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 24:00:00”) EXCEL中使用IF语句来选取不同的值：IF (IFERROR( MATCH(TEXT(日期时间,“yyyy/m/d”),[休息日]) ,0), TEXT(日期时间,“yyyy/m/d 24:00:00”), 日期时间)，获取到检查休息日调整后的时间 样例公式，
开始时间：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 24:00:00”),k3)
Excel中休息日天数计算： NETWORKDAYS.INTL(任务开始日期，任务结束日期，“0000000”) - NETWORKDAYS.INTL(任务开始日期, 任务结束日期, “0000000”, [节假日]) 样例公式，
NETWORKDAYS.INTL(J3,K3,“0000000”)-NETWORKDAYS.INTL(J3,K3,“0000000”,O1:O19)
总的计算时间段样例公式(算出的x.x天) ： 任务结束时间(格式化到24:00:00或23:59:59) - 任务开始时间(格式化到00:00:00) - 任务周期中的休息日天数
IF(IFERROR(MATCH(TEXT(K3,“yyyy/m/d”),O1:O19,0),0),TEXT(K3,“yyyy/m/d 24:00:00”),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)
相关链接
发表评论