Forum Discussion
Count multiple installation start and end date ranges in each week, of each year
I took a look at this yesterday when you first posted and decided to leave it for somebody else to sort through the morass of information you provided. I see that at least 38 other people appear to have done the same.
May I suggest to you that you simplify/clarify what you're asking. The question itself appears to be fairly simple, but you're asking an awful lot of any of us who might want to help--two links to google spreadsheets appear in your posting itself, plus two Excel workbooks, each of which has notations in it meant, I'm sure, as directions, some of them from you and some to you....
It's too much.
You need to remember, the people here who want to help are not being paid for the time we take. It's because we enjoy helping people understand what Excel does; not because we are mind-readers or cryptanalysts.
So if you could create a simple workbook that just represents the core installation database, dates of start and end, and whatever else is necessary to focus in on the precise problem for which you're seeking help, I'm sure that one of those 38+ people would be delighted to offer some suggestions.
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 <=
