Forum Discussion
Count multiple installation start and end date ranges in each week, of each year
That was quick. Thank you.
And maybe I'm slow to grasp this, but I need a bit more clarification. I'm just looking at Frank Hulett and Derek Lewis as an example in asking this:
- When I look at the columns headed "Week Start" and "Week End" it looks to me as if there is one per week; there is no overlap on any of the rows. So is that one install per week? Does that hold true for all other names? Why aren't there Week Start and Week End dates for most of them? Those two columns also don't appear to be part of the same "table" as the others; are they? (Column J is empty and separates them from the rest)
- Nor do I understand what the significance is of those columns headed "Post Install Start Date" and the corresponding "End" date column: are they somehow part of the analysis you're seeking?
I do realize that this is all clear to you, but your data and your question do still require some clarification.
Thank you for all your help and quick responses. I greatly appreciate it.
I uploaded a new spreadsheet with the formula I tried to give you a better idea of what I'm trying to accomplish.
However, if I look at how many installs overlap for Deb Condon for the week of 5/17 to 5/23, by manually counting which ones in the "Post Install Start Date," and "Post Install End Date Columns," then I get a manual count of two for the week of 5/17 to 5/23. Whereas the formula, I tried, says four. This is the same for the other rows. The formula seems off by two or more.
The formula I tried:
=SUMPRODUCT(--($H$3:$H$44>=K3),--($G$3:$G$44<=L3),--($A$3:$A$44=$M$2))danquale I just took a look at this and your formula looks correct. Maybe I don't understand what you are trying to do but it looks like the first 4 jobs overlap that week. The 1st ending in 2021, the 2nd started in april and ending 5/24 and the next 2 starting 5/4 and going to June.
The second week (5/24/2020 - 5/30/2020) shows 6 which is also correct because line 2 ends on 5/24/2020 and is therefore = to the criteria and you defined it as <=
I came up with something, but am not very confident. You'll have to look and see if the numbers seem at all reasonable. They look far too unchanging for each person to be realistic. Except that they do change some toward the end of the year. So you have to be the judge.
I added columns for WEEKNUM rather than the dates, just to make things fit into a smaller table; the same formula could work with the dates as you have them.
This formula:
=COUNTA(FILTER($A$3:$A$218,($A$3:$A$218=$S3)*($H$3:$H$218>=T$2)*($J$3:$J$218<=T$2)))
does rely on the new FILTER function, which is one of several very useful Dynamic Array functions. It should retrieve all the rows that meet the criteria enunciated, which are, in order
Name = name at header column
Dates >= start date and <= end date.
Fortunately for both of us, there is always another way in Excel to accomplish a given goal. So maybe one of the wizards around here will come up with another far more effective solution. I'll be following with interest.
P.S. If you end up posting another version, please do it in conjunction with your new post rather than going up to the start and deleting what was there. It sometimes help to have the whole history, unaltered.
Thank you for your efforts and inventiveness. However, based on my manual calculations in the attached spreadsheet, I have different numbers for Deb Condon, for example, in the tab entitled "SE Grid (DEB)." I put these manual numbers together by eyeballing the date ranges of each install and manually counted how many overlapped in each week. mathetes
