Hi

I am trying to find the actual cycle time of issues as they pass through
different stages. I have attached a sample spreadsheet.

In the example below Colum A lists the different states that an issue can
reside in. Column B is the date when the issue was first identified. Colum C
through I are the time stamps for when the particular issue reaches that
state. Column J is the numbers of days that the issue resides in a last known
state.

The current method that I use is to find the P90 for all complaints in the
€œNew€� state is just look at all open issues in the €œNew€� state at the end of
the week, sum up the days in state and get the P90.The problem here is that I
am ignoring the issues that moved from the New to another state during the
same week.

I would to have a way to compare the created date (column B) to the last
entered date in any state from column C through I and then if the €œWEEKNUM€�
for these two states is the same, find the actual number of days between them
and add that to my P90 calculations.

From my example the P90 for all issues that was in the €œNew€� state during
weeknum 14 should be 8.5. If I just look at the issues that are still in the
open state at the end of the week the P90 shows up as 10.95.

The sample should be pasted in a spreadsheet to get the right format.

State Created date A1 Date B1 Date C1 Date D1 Date E1 Date F1 Date G1
Date Days in state
New 20-Mar-08 12
New 31-Mar-08 5
State B 01-Apr-08 01-Apr-08 4
State G 01-Apr-08 01-Apr-08 4
State B 01-Apr-08 03-Apr-08 4
State B 02-Apr-08 03-Apr-08 3
New 03-Apr-08 2
State G 03-Apr-08 04-Apr-08 04-Apr-08 04-Apr-08 2
State D 04-Apr-08 04-Apr-08 1
New 04-Apr-08 1
State B 05-Apr-08 05-Apr-08 0