Forum Discussion
Solved
Need to graph records that fall between a start and end date over time
I have a data set where each record (case) has a start date (column B) and an end date (column D). I want to know how many records (cases) were active in any given month so that I can chart active c...
Sergei switched the conditions. Here is a sample workbook with my formula and the corrected version of Sergei's formula.
Let's say the start dates are in B2:B100 and the end dates in D2:D100.
Enter the first day of each month you want to consider in a series of cells, for example 1-Jan-2021 in M2, 1-Feb-2021 in M3, ..., 1-Dec-2021 in M13. You can format these cells as mmm or mmmm so that they display only the month, if you wish.
In N2, enter the formula
=SUMPRODUCT(($D$2:$D$100>=M2)*($B$2:$B$100<=EOMONTH(M2,0)))
Fill down to N13.
It did work, I had to tweak it (it was a lot of data). Now I have a new problem...
A case that has no end date must also be counted, how do I do that?
A case that has no end date must also be counted, how do I do that?
Here is a new version.
I'm running into another issue, the data is over multiple years so that solution is counting everything. I added a file so you can see the actual data set. Thank you.
- Thank You So Much!