Calculate Days Since Last Stage, where some stages don't occur

[alithebarman]

Hi Guys, first post here,

I've trawled a few forums with this issue, but admittedly I could have looked a little harder, so apologies if this is easily answered on another thread.

I'm monitoring a set of projects and want to report on the number of days taken since the last step, which is are set out in a numeric order (i.e. step 1, 2, 3 etc). However not every project needs to go through every step (i.e. 1, 2, 7, 18 etc).

I have a table with rows as a record of each event with other projects mixed in together, i.e.

Row 1 - Project A - Step 3 - 04/11/10
Row 2 - Project A - Step 4 - 05/11/10
Row 3 - Project B - Step 3 - 06/11/10 etc

I have a pivot combining all this data and apart from setting up a set of nested IFs (I have 39 steps in total so a nested 39 IF formula doesn't appeal) counting back each step until in finds a cell with a date, I can't think what else to do.

I want to be able to say, "this project took X days to go to Step 7, then X days to get to Step 8, this project didn't do Step 9, but it took X days to get to Step 10 (from Step 8)".

It also needs to accommodate that some events might occur on the same day and therefore be 0 days between the last step.

I'm running 2007 and have a basic understanding of VBA, though I would prefer not to use it as the file is going around several Government departments where IT security is pretty tight.

I hope that's clear.

Will sincerely appreciate any advice you can offer.

Ali

 

[Andrew Fergus]

Hi

Assuming the data is laid out something like this:

  A       B    C         
1 Project Step Date      
2 A       1    1/01/2010 
3 A       2    1/01/2010 
4 B       1    2/01/2010 
5 B       2    3/01/2010 
6 A       3    6/01/2010 
7 B       4    7/01/2010 
Sheet2
[Table-It] version 09 by Erik Van Geit

Then how about something like this:

  G       H   I           J         K       
1 Summary                                   
2 Project B                                 
3 Task    Row Status      Date      Elapsed 
4 1       4   Completed   2/01/2010         
5 2       5   Completed   3/01/2010 1       
6 3       0   Skipped                       
7 4       7   Completed   7/01/2010 4       
8 5       0   Not Started                   
9 6       0   Not Started                   
Sheet2
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
H4:H9 =SUMPRODUCT(--($A$1:$A$10=$H$2),--($B$1:$B$10=G4),ROW($A$1:$A$10))
I4:I9 =IF(H4>0,"Completed",IF(MAX(H5:H$21)>0,"Skipped","Not Started"))
J4:J9 =IF(H4>0,INDEX(C:C,H4),"")
K5:K9 =IF(H5>0,J5-MAX(J$4:J4),"")
[Table-It] version 09 by Erik Van Geit

Enter the Project ID into cell H2. Copy the formulas in row 9 down say that you have all 18 tasks shown. Any time you want to look at a project, just change the project ID in cell H2.

I have only given this a basic test so hopefully there aren't any major problems.

PLEASE NOTE you will need to change the formula in column H to cover more than just the 10 rows I have included in my example formula. Also, take note of where I have and have not used the $ symbol for absolute references - some may look funny but they have been written that way on purpose.

I have assumed the tasks take place sequentially in date order and are also stored that way - if this is not the case then let me know. Please note column H is a helper column to help find the relevant row of data - you can hide but not delete this column.

HTH
Andrew

P.S. Welcome to MrExcel!!

 

[alithebarman]

Excellent,

Thanks for your assistance there, I will play around with that and see how I go.

Many thanks again!