|Subject:||Tricky counting question|
|Posted by:||Jay Weiss (rjwei…@gmail.com)|
|Date:||1 May 2006|
I have a spreadsheet in which each row represents a different process.
I have three columns that represent the process start date, the process
end date, and the process end time. What I'd like to do is come up
with a count for each row of how many *other* processes were between
their start date and end date/time when each process ended.
Here's an example:
Row 1: StartDate 04/01/06 EndDate 04/05/06 EndTime: 20:00:00
Row 2: StartDate 04/02/06 EndDate 04/10/06 EndTime: 07:00:00
Row 3: StartDate 04/02/06 EndDate 04/04/06 EndTime: 10:00:00
Row 4: StartDate 04/06/06 EndDate 04/10/06 EndTime: 05:00:00
When the process in Row 1 ends, the process in Row 2 is active, so the
count for Row 1 is 1.
When the process in Row 2 ends, none of the other processes is active
(Row 4 ended two hours earlier), so the count for Row 2 is 0.
When the process in Row 3 ends, the processes in Rows 1 and 2 are
active, so the count for Row 3 is 2.
When the process in Row 4 ends, the process in Row 2 is active, so the
count for Row 4 is 1.
Assuming StartDate is Column A, EndDate is Column B, and EndTime is
Column C, how do I calculate the count and put it in Column D?
Thanks to anyone who is so gracious as to help out with this tricky