I'm pretty decent with Excel, but I've been struggling with this one for about three weeks now. Hoping someone has some insight or a new way to attack the problem!
I have a collection of historical data for a group of requisitions. The pertinent data for this portion of the project is the req. creation date, and purchase order creation date (this is the conversion date, and thus the closure of the requisition).
I need to figure out, for any particular day in history, how many requisitions we had open on that particular day.
The only way I can visualize the solution is to have some kind of Gantt chart showing the duration of each order over the period of several years. This won't do, for several reasons, but the main one is that we have close to 100,000 rows all with unique open and closure dates.
I have a feeling that some kind of array formula with SUMPRODUCT may be able to compare the individual dates associated with each order against the particular day in history in question - but I can't wrap my head around a solution.
Any help would be greatly appreciated! I'm sure I've been less than clear in my explanation. Please let me know how else I can help - but I did want to get the ball rolling. Thank you all!
-Kevin
Bookmarks