查找许多重叠时间的总持续时间 [英] Find total duration of many overlapping times

问题描述

我有一份员工时间表的日期和时间列表.时间开始于F列，结束于G列.有时项目的时间重叠.员工没有为重叠的项目获得报酬，但是我们需要分别跟踪每个项目.我希望能够查看E，F和G列并找到任何重叠的项目，并返回一个时间条目.在下面的示例中，请注意第1行不与其他行重叠，但是第2-6行中存在一系列重叠的条目.它们不一定全部重叠，但更像是链条".我想写一个公式(不是脚本)来解决这个问题.

I have a list of dates and times for employee time sheets. The times begin in column F, and end in column G. Sometimes there are overlapping times for projects. The employee does not get paid for overlapping projects, yet we need to track each project separately. I would like to be able to look at columns E, F and G and find any overlapping projects, and return a single time entry. In the example below, notice that line 1 does NOT overlap with the others, but that there is a series of overlapping entries in lines 2-6. They don't necessarily all overlap, but are more like a "chain." I want to write a formula (not a script) to solve this.

+---+------------+------------+----------+
|   |     E      |       F    |    G     |
+---+------------+------------+----------+
| 1 | 10/11/2017 | 12:30 PM   |  1:00 PM |
| 2 | 10/11/2017 |  1:00 PM   |  3:00 PM |
| 3 | 10/11/2017 |  2:15 PM   |  6:45 PM |
| 4 | 10/11/2017 |  2:30 PM   |  3:00 PM |
| 5 | 10/11/2017 |  2:15 PM   |  6:45 PM |
| 6 | 10/11/2017 |  3:00 PM   |  6:45 PM |
+---+------------+------------+----------+

我想评估这些列，并在一系列重叠的最后一行上返回每个链"的总持续时间.在下面的示例中，我们将其放在H列中.该序列为从第2行开始到第6行(下午1点至下午6:45)结束的系列查找5.75小时.

I would want to evaluate these columns and return the total duration of each "chain" on the final line of the series of overlaps. In my example below, we'll put that in column H. It finds 5.75 hours for the series that begins in row 2 and ends in row 6 (1 pm to 6:45 pm).

+---+------------+------------+----------+------------+
|   |     E      |       F    |    G     |      H     |
+---+------------+------------+----------+------------+
| 1 | 10/11/2017 | 12:30 PM   |  1:00 PM |    0.5     |
| 2 | 10/11/2017 |  1:00 PM   |  3:00 PM |  overlap   |
| 3 | 10/11/2017 |  2:15 PM   |  6:45 PM |  overlap   |
| 4 | 10/11/2017 |  2:30 PM   |  3:00 PM |  overlap   |
| 5 | 10/11/2017 |  2:15 PM   |  6:45 PM |  overlap   |
| 6 | 10/11/2017 |  3:00 PM   |  6:45 PM |   5.75     |
+---+------------+------------+----------+------------+

我曾尝试编写查询，但一开始总是发现自己.如果有人有建议，我很想知道！预先谢谢你.

I've tried writing queries, but keep finding myself back at the beginning. If anyone has a suggestion, I'd love to know it! Thank you in advance.

尼尔

推荐答案

我的解决方案

要解决这个问题，我需要增加2列:

My Solution

To solve this I need 2 extra columns:

当一端在另一端内时，两行重叠:

Two lines overlap when one end is inside the other:

我做了一个 query 公式对此进行了检查:

I made a query formula to check this:

= if(QUERY(ArrayFormula({value(E1:E + F1:F)，VALUE(E1:E + G1:G)})，选择计数(Col1)，其中Col1 ＜& value(G1 + E1-1/10 ^ 4)&"并且Col2>& value(F1 + E1 + 1/10 ^ 4)&"标签Count(Col1)``，0)> 1，"重叠，"确定)

向下拖动公式.结果是列:

Drag the formula down. The result is column:

ok
overlap
overlap
overlap
overlap
ok
ok
overlap
overlap
overlap
overlap
ok

在公式中:

• value 用于比较数字.必须比较每个参数:日期和时间.
由于 query 中的不精确性，使用
• -1/10 ^ 4 和 + 1/10 ^ 4

• value is used to compare numbers. Must compare each pare: date + time.
• -1/10^4 and +1/10^4 is used because of imprecision in query

这部分很棘手.仅当数据按照示例中的顺序排序时，我的解决方案才有效.

This part is tricky. My solution will only work if data is sorted like in the example.

在单元格 I1 中输入1.在单元格 I2 中输入公式:

Enter 1 in cell I1. In cell I2 enter the formula:

= if(or(and(H1 = H2，H2 ="overlap")，and(H2 ="ok"，H1 ="overlap"))，I1，I1 + 1)

向下拖动公式.结果是列:

Drag the formula down. The result is column:

1
2
2
2
2
2
3
4
4
4
4
4

Step3.获取持续时间

在J4中粘贴并复制公式:

Step3. Get Durations

In J4 paste and copy down the formula:

= if(H1 ="ok"，round(QUERY(ArrayFormula({value(E:E + F:F)，VALUE(E:E + G:G)，I:I})，选择max(Col2)-min(Col1)，其中Col3 =& I1&标签max(Col2)-min(Col1)")* 24,2)，")

查询按组获取最大持续时间，在步骤2中找到.

The query gets max durations by groups, found in step2.

使用
• round 是因为 query
中的不精确

• round is used because of imprecision in query

这篇关于查找许多重叠时间的总持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！