给定日期范围，计算该范围所在的每个支付期的工作日数

Question

我在Excel2007中有一个大致包含6500+行的列表。每行表示人员请伤残假的时间，包括开始日期和结束日期。如果没有终止日期，则终止日期设置为12/31/9999。下面是一些值的示例：

Workers Compensation    4/7/2009    12/31/9999
Workers Compensation    5/21/2009   12/31/9999
Short Term Disability   8/27/2009   10/7/2009
Short Term Disability   6/22/2009   7/15/2009
Short Term Disability   1/21/2009   10/4/2009
Short Term Disability   4/8/2009    6/14/2009
Short Term Disability   6/19/2009   10/5/2009
Short Term Disability   8/19/2009   12/31/9999
Short Term Disability   5/30/2009   6/18/2009
Short Term Disability   7/9/2009    7/20/2009

第一个日期是他们休假的日期；第二个日期是他们休假回来的日期。

我正在尝试分解每一行，这样我就可以计算出此人在每个支付期休假的天数。我们每两周支付一次，2009年的第一次支付涵盖12/14/08和12/27/08期间，此后每两周递增一次(支付#2涵盖12/28/08到1/10/09等)。

例如，我知道第一行数据涵盖了支付期200909到当前支付期(我们当前的支付期是200924)。我还知道期间200910到200924应该有10天作为计算数字(因为每个工资有10个工作日)，并且支付期200909的计数应该是2(因为在那个支付期中有2个工作日与员工休假时重叠)。

我想用一个公式来计算，而不是写代码。有什么想法吗？提前谢谢。

Answer 1

透视表方法。输入这些公式

G1:12/26/2008
G2:=G1+14

向下填到第28行。然后

A14: Date
A15: 1/21/2009
A16: =A15+1
B14: Count
B15: {=SUM(($B$1:$B$10<=A15)*(WEEKDAY(A15)<7)*(WEEKDAY(A15)>1)*($D$1:$D$10>=A15))}
C14: Week
C15: =INDEX($G$1:$G$28,MATCH(A15+13,$G$1:$G$28))

向下填充A、B和C公式到第321行，以在2009年11月23日停止(如果需要，也可以更远)。

选择A14:C321并创建一个数据透视表。在行区域输入'Week‘，在数据区域输入'Count’。

Answer 2

在这里，我做了几个假设：

1. 休假类型在A列下。开始日期和结束日期在B列和C列下。
2. 行从第1行开始。
3. 您只关心2008年12月14日开始的支付期。
4. 您没有考虑银行/公共假日(尽管考虑到它们并不是什么大问题)。
5. 员工的休假包括结束日期。

我使用了几个单元来解决这个问题：

• In cell D1：=IF(C1=DATE(9999,12,31)，TODAY()，C1)
• In cell E1：=B1 - MOD(B1-DATE(2008,12,14),14) -1\f25
• In cell F1：=D1 - MOD(D1-DATE(2008,12,14),14) - 1
• In cell G1：=(F1-E1)/14 -1
• 单元格H1：=(NETWORKDAYS(E1，B1) +NETWORKDAYS(，#EN20#)+(- 2) * 10)/ G1

Cell D1确定休假是否正在进行。单元格G1计算开始支付期和结束支付期之间的支付期数量。单元格H1包含您要查找的答案。

您可以将这些单元格复制到其他行。另外，如果可行的话，我会考虑用that()代替12/31/9999代替C列。

已更新

我注意到，如果未指定结束日期，则与今天日期所属的支付期间相对应的列的值应为10。此外，我不能确保在假期所属的第一个支付期间，我应该返回该人员在该期间的休假天数。考虑到上述情况，以及所提供的澄清，我提出了以下解决方案。

我做了与上面相同的假设，只是假设2被替换为行从第2行开始的假设。

我定义了以下名称：

• FirstPayPeriod：=DATE(2008，12,14)
• PayPeriodLength：=14
• MaxDaysPerPeriod：= PayPeriodLength)
• NoEndDate：( FirstPayPeriod，FirstPayPeriod+NETWORKDAYS =DATE(9999，12，31)

我定义了以下单元格，并复制了所需的行数：

今日单元格D2：=IF(C2 = NoEndDate，FirstPayPeriod + PayPeriodLength *ROUNDUP((()- FirstPayPeriod)/PayPeriodLength，0) - 1，C2)

• 单元格E2：=NETWORKDAYS(B2，D2)

然后，我为第一行定义了以下单元格：

单元格F1：=FirstPayPeriod

• Cell G1：=F1 + PayPeriodLength

然后，我将单元格G1复制到我想表示的任意多个支付期间的列中。当然，单元格F1和右侧的每个后续单元格指定该列表示的支付期的开始日期。

最后，我定义了以下单元格，并将其复制到与支付期间一样多的右侧列(除了最后一个支付期间之外的-参见下面的)，以及向下复制到与数据一样多的行：

• 单元格F2：=IF(OR(G$1 <= $B2，F$1 > $D2)，0，MIN($E2，NETWORKDAYS(F$1，G$1 - 1)，NETWORKDAYS($B2，G$1)，NETWORKDAYS(F$1，$D2))

对于上一个支付期，我做了以下工作。假设列AD保存最后一个支付期的数据：

• 单元格AD2：=IF(AC$1 > $D2，0，MIN($E2，NETWORKDAYS( AD$1，AD$1+ PayPeriodLength - 1)，NETWORKDAYS(AC$1，$D2))

然后，我复制了尽可能多的数据行。

Answer 3

输入以下公式

G1:12/26/2008
G2:=G1+14

向下填到第28行。然后

D1:=IF(C1=DATE(9999,12,31),TODAY(),C1)
E1:=NETWORKDAYS(B1,INDEX($G$1:$G$28,MATCH(B1+13,$G$1:$G$28)))
F1:=10-NETWORKDAYS(D1+1,INDEX($G$1:$G$28,MATCH(D1+14,$G$1:$G$28)))

并在必要时填满。然后

H1:{=SUM(($B$1:$B$10<=G1-14)*($D$1:$D$10>G1))}
I1:{=SUM(($B$1:$B$10<G1)*($B$1:$B$10>=G1-14)*($E$1:$E$10))}
J1:{=SUM(($D$1:$D$10>G1-14)*($D$1:$D$10<=G1)*($F$1:$F$10))}
K1:=SUM(H1*10,I1,J1)

请注意，方括号中的公式需要输入数组-不要输入方括号，而是使用control+shift+enter而不是只输入enter来提交公式。

显然，为了简洁起见，您可以将一组列组合在一起。