Google表格公式可计算具有不同开始/结束日期，重叠和间隔的任务的实际总工时 [英] Google Sheets Formula to calculate actual total duration of tasks with different start/end dates, overlaps, and gaps

问题描述

我知道我该如何使用自定义函数/脚本来执行此操作，但我想知道是否可以使用内置公式来完成此操作.

I know I how to do this using a custom function/script but I am wondering if it can be done with a built-in formula.

我有一个包含开始日期和结束日期的任务列表.我想计算在所有任务上花费的实际工作日(NETWORKDAYS).

I have a list of tasks with a start date and end date. I want to calculate the actual # of working days (NETWORKDAYS) spent on all the tasks.

• 工作时间可能会重叠，所以我不能只计算每个任务花费的天数
• 任务之间可能会有差距，所以我不能仅仅找到开始和结束之间的区别.

例如，让我们使用这些:

For example, let's use these:

| Task Name | Start Date | End Date   | NETWORKDAYS |
|:---------:|------------|------------|:-----------:|
|     A     | 2019-09-02 | 2019-09-04 |      3      |
|     B     | 2019-09-03 | 2019-09-09 |      5      |
|     C     | 2019-09-12 | 2019-09-13 |      2      |
|     D     | 2019-09-16 | 2019-09-17 |      2      |
|     E     | 2019-09-19 | 2019-09-23 |      3      |

在这里是视觉上的:

现在:

• 如果您总计NETWORKDAYS，您将获得15
• 如果您在2019-09-02和2019-09-23之间计算NETWORKDAYS，您将获得16

• If you total the NETWORKDAYS you'll get 15
• If you calculate NETWORKDAYS between 2019-09-02 and 2019-09-23 you get 16

但是实际持续时间是13:

But the actual duration is 13:

• A和B有点重叠
• B和C之间有差距
• D和E之间有差距

如果我要编写一个自定义函数，则基本上我将采用所有日期，对它们进行排序，找到重叠的部分并删除它们，并考​​虑差距.

If I was to write a custom function I would basically take all the dates, sort them, find overlaps and remove them, and account for gaps.

但是我想知道是否有一种方法可以使用内置公式来计算实际工期?

But I am wondering if there is a way to calculate the actual duration using built-in formulas?

推荐答案

请确定，为什么不这样做

sure, why not:

=ARRAYFORMULA(COUNTA(IFERROR(QUERY(UNIQUE(TRANSPOSE(SPLIT(CONCATENATE("×"&
 SPLIT(REPT(INDIRECT("B1:B"&COUNTA(B1:B))&"×", 
 NETWORKDAYS(INDIRECT("B1:B"&COUNTA(B1:B)), INDIRECT("C1:C"&COUNTA(B1:B)))), "×")+
 TRANSPOSE(ROW(INDIRECT("A1:A"&MAX(NETWORKDAYS(B1:B, C1:C))))-1)), "×"))), 
 "where Col1>4000", 0))))

这篇关于Google表格公式可计算具有不同开始/结束日期，重叠和间隔的任务的实际总工时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！