从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?

注意:

• 工作日为星期一至星期五(仅影响
  应该考虑工作日)
• 不应将假期视为
  计算.
• 请假总计包括开始和结束时间
  日期.

• Working days are from Monday - Friday (leave impact from only
  weekdays should be considered)
• Holidays should not be considered in
  the calculation.
• Total leave will be inclusive of start and end
  dates.

在此方面的任何帮助，我将不胜感激.

I'd appreciate greatly any help on this.

谢谢！

推荐答案

我在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;\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;\0"))

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

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

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