使用Excel计算--任务完成总工作日时长

(Owed by: 春夜喜雨 http://blog.csdn.net/chunyexiyu)

引言

计算任务完成时间周期，和计算金钱一样，是一个比较细致严谨的工作。

通常，我们可能以为，完成周期形如：
任务完成周期 = 任务结束时间 - 任务开始时间

但是一般情况下，对于正常社会企业，通常都是要把排除掉休息日，来计算的任务完成周期。

而要排除休息日就是一个比较复杂的事情了。

1. 识别工作日与休息日

首先节假日本身的规律性不是很强，每年都会不同，我们知道，节假日的计算要考虑几种情况

1. 周末双休日；
2. 因法定节假日调整的休息日(非周末)；
3. 因法定节假日调整的工作日(周末)；

所以

一年中的休息日 = 周末双休日总和 + 因法定节假日调整的休息日(非周末) - 因法定节假日调整的工作日(周末)
一年中的工作日 = 一年的天数总数 - 一年中的休息日

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天时间）

综合所有的场景之后，得出的综合公式如下：

1. 如果任务结束时间是休息日，任务结束时间调整到24:00:00或23:59:59，工作日时保持不变；
2. 如果任务开始时间是休息日，任务开始时间调整到00:00:00，工作日时保持不变；
3. 任务完成周期 = 调整后任务结束时间 - 调整后任务开始时间 - (任务周期中的休息日天数 * 1天时间）

3. 计算公式

1. 获取调整后时间：
   判定是否是休息日，可以使用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)

2. Excel中休息日天数计算：
   NETWORKDAYS.INTL(任务开始日期，任务结束日期，“0000000”) - NETWORKDAYS.INTL(任务开始日期, 任务结束日期, “0000000”, [节假日])
   样例公式，

NETWORKDAYS.INTL(J3,K3,“0000000”)-NETWORKDAYS.INTL(J3,K3,“0000000”,O1:O19)

3. 总的计算时间段样例公式(算出的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)