![让网络天数准确反映两个日期之间的天数](https://linux22.com/image/1257337/%E8%AE%A9%E7%BD%91%E7%BB%9C%E5%A4%A9%E6%95%B0%E5%87%86%E7%A1%AE%E5%8F%8D%E6%98%A0%E4%B8%A4%E4%B8%AA%E6%97%A5%E6%9C%9F%E4%B9%8B%E9%97%B4%E7%9A%84%E5%A4%A9%E6%95%B0.png)
我正在使用NETWORKDAYS
公式计算一系列不同交易日期的两天之间的天数。我希望计数排除每个交易日期的周末和节假日。但是,结果并不像我预期的那样。
例如,1 月 26 日至 1 月 27 日之间的交易应为 1 天,但被算作 2 天。有没有简单的方法来计算天数?我曾尝试使用一个简单的公式,例如=C9-C8
,这将给我 1 天。因为我有大量数据要处理,所以使用这种方法并排除周末和节假日会非常困难。
=NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)
例子:
Date on Column C Result on Column F Row 7: 1/20/10 12:00 AM
Row 8: 1/26/10 12:00 AM 5 Row 9: 1/27/10 12:00 AM 2
Row10: 1/28/10 12:00 AM 2
答案1
我认为这是因为它在计算中同时包括了开始日期和结束日期。所以我认为你非常接近,只需要减去你的计数并减去 1。像这样 -
=(NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)) - 1
这有意义吗?
答案2
这是一个老问题,多年来一直在收集答案。现有的答案在某些情况下可以产生正确的结果,但问题并不像这些答案描述的那么简单。
NETWORKDAYS 为您提供一段时期内的工作日数,包括第一天和最后一天。在某些情况下,这并不是您真正需要的度量,因此公式需要调整。但是,有很多不同的情况,既包括您可能需要如何调整它,也包括开始日期和结束日期相对于排除的天数。实际上,没有一个调整后的公式可以为每种情况提供正确的答案。我将列出工具来为您的特定情况生成所需的调整结果。
如果目标不是日期范围内包含的工作日数,则可能需要处理以下一些变化:
开始和结束时间都在同一天。NETWORKDAYS 会得出
1
当天是工作日还是0
非工作日的结果。这通常是理想的结果,但有些变体会说,如果是工作日,结果应该是0
当天的一小部分(见下一条)。对于工作规划,开始日和结束日可能具有特殊含义。工作可以在开始日分配或启动,但该日不被视为完成任务的资源。在这种情况下,将从资源日池中减去开始日。
另一方面,结束日期可以视为完成日期,结果将在当天早些时候公布。在这种情况下,结束日期将从池中减去。
还有一种情况是考虑“平均天数”。一项任务的工作在开始日的某个时间开始,并在结束日的某个时间完成。因此,周一和周二发生的任务被视为周一占用半天,周二占用半天,总资源为一天。调整将从开始日期和结束日期中减去半天。
但这一切都取决于开始和结束日期是否为工作日。如果某个时间段从周日开始,到周六结束,则可获得整整一周的工作日。无需进行调整。
如果期间开始或结束于非工作日,而该期间的另一端是工作日,则调整将取决于如何考虑开始和结束日期(参见上一条)。
因此,如果您想将 NETWORKDAYS 用于其他目的,您需要构建一个公式,以正确反映您希望如何查看开始和结束日期。您可以通过再次使用 NETWORKDAYS 进行调整来简化该任务。
如果您使用开始日期或结束日期来表示日期范围,则 NETWORKDAYS 将返回0
该天是非工作日还是1
工作日。例如:
NETWORKDAYS(C8,C8,holiday!$A$106:$A$117)
将返回开始日期的0
或1
值。您可以使用它来构建一个调整,以自动调整该日期是否为工作日。示例:
如果开始日期是工作日,则排除开始日期:
=NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-NETWORKDAYS(C8,C8,holiday!$A$106:$A$117)
如果结束日期是工作日,则排除结束日期:
=NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-NETWORKDAYS(C9,C9,holiday!$A$106:$A$117)
如果开始日期和结束日期是工作日,则排除半天:
=NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-(NETWORKDAYS(C8,C8,holiday!$A$106:$A$117)+NETWORKDAYS(C9,C9,holiday!$A$106:$A$117))/2
请注意,如果第一天或最后一天是非工作日,则最终会得到包含半天的值。对于这种情况,如果您只对整天感兴趣,则需要按所需方向添加舍入。
如果您可以有一天的时间段,则需要决定如何在规则内处理该时间段;这可能是您定义工作日的一个例外。如果您始终希望将其视为一整天,则可以构建 IF 测试以查看 NETWORKDAYS 结果是否为1
,并分配结果1
而不是您的调整。
如果您可以拥有一天的时间段并且该时间段可能落在非工作日,则可以使用 IF 测试来检查 NETWORKDAYS 是否相等0
,在这种情况下,您可以返回文本警告或触发错误条件。
请注意,NETWORKDAYS 仅使用日期/时间值的日期(整数)部分;任何时间都会被忽略。一个后果是,半天调整(如上例所示)不受任何时间值的影响,并且时间值不能用于创建半天调整。
答案3
我知道这个话题已经很老了,但很多人都需要这个解决方案。我花了至少 7 天的时间来解决这个问题。
假设:
- 开始日期 (SD) = 单元格 A2,结束日期 (ED) = 单元格 B2,假期是以假期命名的范围(我发现这种方式比指定范围更容易,我个人有一个宏可以自动将波兰假期计算到命名范围内)
- 如果 SD 大于或等于 ED,则结果 = 0,
- 计算结果时,第一个工作日始终算作 0(例如,SD 是星期日,那么星期一是第 0 天,星期二是第 1 天,依此类推,
- 如果 ED 不是工作日,那么我们算到最后一个工作日(网络日已经这样做了)
Excel 2013+ 的英文版本(不太记得 quotient() 是什么时候引入的):
=IF(
QUOTIENT(A2,1)>=QUOTIENT(B2,1),0,
IF(
AND(NETWORKDAYS(A2,A2,holidays)=0,NETWORKDAYS(B2,B2,holidays)=0,NETWORKDAYS(A2,B2,holidays)=1),
NETWORKDAYS(A2,B2,holidays),
IF(NETWORKDAYS(A2,B2,holidays)=0,0,NETWORKDAYS(A2,B2,holidays)-1)
))
每个部分的作用:
= 如果 ( 商 (A2,1) >= 商 (B2,1) , 0
如果单元格还包含时间,则仅取日期部分并进行比较,如果为真则返回 0
AND( NETWORKDAYS( A2, A2, 假期 ) = 0, NETWORKDAYS( B2, B2, 假期 ) = 0, NETWORKDAYS( A2, B2, 假期 ) = 1 )
这是唯一一种不应该从结果中减去 1 的情况
网络日(A2、B2、节假日)
如果为真则不减去(保持 networkdays() 不变)
IF(NETWORKDAYS(A2,B2,假期) = 0,0,NETWORKDAYS(A2,B2,假期) - 1)
在其他所有情况下都减去,但检查结果是否为负数。
Excel 2013+ 的波兰语版本(不包括 CZ.CAŁK.DZIELENIA() 函数):
=JEŻELI(
CZ.CAŁK.DZIELENIA(J1;1)>=CZ.CAŁK.DZIELENIA(K1;1);0;
JEŻELI(
ORAZ(DNI.ROBOCZE(J1;J1;dni_wolne)=0;DNI.ROBOCZE(K1;K1;dni_wolne)=0;DNI.ROBOCZE(J1;K1;dni_wolne)=1);
DNI.ROBOCZE(J1;K1;dni_wolne);
JEŻELI(DNI.ROBOCZE(J1;K1;dni_wolne)=0;0;DNI.ROBOCZE(J1;K1;dni_wolne)-1)
))
答案4
网络日不考虑星期六和星期日,例如开始日期:星期五结束日期:星期一(网络日-1 将得出“1”,这正是您想要的),但是当开始日期:星期六或星期日结束日期:星期一(网络日-1 将得出“0”,这不是正确答案)时,我们必须使用工作日函数自行调整这些周末。