在excel中的2个不同列中查找和添加重叠的日期 [英] Finding and adding together overlaps in dates from 2 different columns in excel

问题描述

我的 excel 工作簿上有 2 个表.一个跟踪任务，一个跟踪休假.我想看看休假对每个任务的影响(即属于任务开始和结束日期的资源的休假总天数)

I have 2 tables on my excel workbook. One to track tasks and one to track leave. I want to see the impact leave would have on each tasks (i.e. the total number of leave days of a resource that would fall within the start and end dates of a task)

左边的表格包含任务.右边的桌子上有叶子.我可以在E"列中使用什么公式来获得与特定资源对应的任务的开始和结束日期内的休假总天数?

The table on the left contains the tasks. The table on the right contains the leaves. What formula can I use in the column 'E' to obtain the total number of leave days that would fall within the start and end dates of a task corresponding to a particular resource?

注意事项:

• 工作日是从周一到周五(只留下影响
  应该考虑工作日)
• 假期不应被考虑在
  计算.
• 总休假将包括开始和结束
  日期.

我将不胜感激.

谢谢！

推荐答案

我在 E2 中使用了这个数组公式"，确认了 CTRL+SHIFT+ENTER 复制下来

I used this "array formula" in E2 confirmed with CTRL+SHIFT+ENTER and copied down

=SUM((H$2:H$7=A2)*TEXT(NETWORKDAYS(IF(I$2:I$7>C2,I$2:I$7,C2),IF(J$2:J$7)<D2,J$2:J$7,D2)),"0;"))

这会检查资源是否匹配，并且对于所有匹配的行在叶子和任务之间的任何重叠日期期间执行 NETWORKDAYS 计算(忽略周末).

This checks that the Resource matches, and for all matching rows does a NETWORKDAYS calculation (which ignores weekends) within any overlapping date periods between leaves and tasks.

如果没有重叠，则返回负值，因此使用 TEXT 函数将负值转换为零.

Where there is no overlap a negative value is returned so TEXT function is used to convert the negatives to zeroes.

SUM 函数对结果数组求和，为您提供所需的总数 - 见截图

SUM function sums the resulting array to give you the required total - see screenshot

如果你想在离开"结束日期字段中允许空白，那么你可以使用这个调整后的版本，它会假设结束日期是今天

If you want to allow blanks in the "leaves" end date field then you can use this adjusted version which will assume end date is today

=SUM((H$2:H$7=A2)*TEXT(NETWORKDAYS(IF(I$2:I$7>C2,I$2:I$7,C2),IF(IF(J$2:J$7="",TODAY(),J$2:J$7)<D2,IF(J$2:J$7="",TODAY(),J$2:J$7),D2)),"0;"))

...或者您可以用另一个日期或包含日期的单元格引用替换 TODAY() 的两个实例

...or you can replace the two instances of TODAY() with another date or cell reference containing a date

这篇关于在excel中的2个不同列中查找和添加重叠的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！