将一个日期范围内属于第二个日期范围内的天数总和 [英] Sum the number of days in one date range which fall within a second date range

问题描述

我有两列日期.一个是搬入日期，另一个是搬出日期.我想弄清楚在第二个日期范围内租户在那里住了几天.例如:在7月份，我们总共有多少个卧床日"? 2016年7月1日至7月31日

I have two columns of dates. One is the move in date and the other the move out date. I want to figure out how many days the tenant was there during a second date range. for example: how many total "bed days" did we have in the month of July? 7/1/2016-7/31/2016

此函数计算每个租户每个月的工作天数，但是如果我可以将整个计算结果放入一个单元格而不为每个月创建一个虚拟列，我希望这样做.

This function calculates the number of days each tenant was there each month but I would like it if I could get the entire calculation into one cell without creating a dummy column for each month.

=MAX(0,MIN(EOMONTH($B$2,0),I14)-MAX($B$2,H14))

我试图更改一些内容并将其用作数组函数，但是它不起作用.我是数组函数的新手，所以我做错了.

I tried to change a few things and use it as an array function but it is not working. I am very new to array functions so I may be doing it completely wrong.

=SUM(MAX(0,MIN(EOMONTH($B$2,0),I:I)-MAX($B$2,H:H)))

非常感谢您的帮助！让我知道您是否也需要更多信息.

any help is much appreciated! Let me know if you need more info too.

推荐答案

坏消息-您不能在这样的数组公式中使用MAX和MIN，因为与其将H和I视为两个数组，还不如将H和I视为两个数组大的长数组，您只会从中获得一个价值.

Bad news - you can't use MAX and MIN in an array formula like this because instead of treating H and I as two arrays it just treats them as one big long array and you only get one value out of them.

您还需要在原始公式中加1，因为如果他们在一个月的最后一天(例如)搬进来，它仍应算作一天.

You also need to add 1 to your original formula because if they moved in on the last day of the month (say) it should still count as one day.

如果用IF语句替换MAX和MIN，则会得到类似的内容

If you replace the MAX and MIN with IF statements you get something like this

=SUM(IF(IF(EOMONTH($B$2,0)<IF(I2:I10="Active",TODAY(),I2:I10),EOMONTH($B$2,0),IF(I2:I10="Active",TODAY(),I2:I10))-IF($B$2>H2:H10,$B$2,H2:H10)<0,0,
IF(EOMONTH($B$2,0)<IF(I2:I10="Active",TODAY(),I2:I10),EOMONTH($B$2,0),IF(I2:I10="Active",TODAY(),I2:I10))-IF($B$2>H2:H10,$B$2,H2:H10)+1))

必须使用 Ctrl Shift Enter

如果您不熟悉数组，一个有用的提示是不要在数组公式中包含比所需数量更多的行，因为这样做会很慢，请先对少量的行进行测试，以便可以使用如果遇到麻烦，请评估公式.

A useful tip if you are new to arrays is not to include more rows in an array formula than you need to because it will be slow, and test it first on a small number of rows so that you can step through it using Evaluate Formula if you run into trouble.

这篇关于将一个日期范围内属于第二个日期范围内的天数总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！