# VBA to calculate critical path length

• ### Question

• I'm adding to our homegrown schedule analyse tool and am looking for a formula that I can use to calculate the critical path length and subsequently the Critical Path Length Index. All of the references I've found give some examples with numbers for it (CPL), but they don't include a description as to how the number(s) was derived.

Any thoughts?  thanks in advance for any suggestions.

Dan

Friday, September 4, 2020 9:16 PM

### All replies

• Dan,

Never heard of CPL or CPLI. The DCMA really does love their metrics, I guess it keeps somebody employed.

I found this reference which has the formula and an example. Seems like it would be simple enough to calculate, just look at all tasks with the Critical field "yes", add up the working days of duration, subtract (or dad) the days of Total Slack. Of course tasks with constraints throw a wrench into the path.

Hope this helps.

John

Saturday, September 5, 2020 8:12 PM
• Dan,

If you look around, you’ll find a 621-slideshow (complete with embedded .mpp, .xls, and .doc files) covering DCMA’s initial training guidance (circa 2009) for the 14-point assessment.  It reflects a manual approach but provides useful background for vba calculations.  In any case, the CPLI calculation first requires removal of any “hard” constraints and deadlines and explicit selection of a designated completion milestone.  Thus, CPL and CPLI are not standard output metrics for any given schedule model.  That said, general-purpose assessment tools can routinely assume their way out of such constraints.

For your own code, CPL is the time interval between the Status Date and the current forecast (early) finish date of the designated completion milestone.  In vba, that's easy to calculate using the Application.DateDifference method.

The formula for CPLI is shown in the reference John shared: CPLI = (CPL + TF)/CPL.  Even though the TF in that formula is commonly described as Total Float, it’s really Project or Contract Float and is best computed as the time interval between the current forecast (early) finish date and the baseline finish date of the designated completion milestone.  The Application.DateDifference method is also good for this calculation, but your code will need an if-then-else block to handle the sign change for behind-schedule work. (Application.DateDifference doesn’t calculate negative intervals.)

Good luck, tom

Monday, September 7, 2020 8:29 PM
• Tom,

Just to make sure I'm not over thinking this, based your comments

1) the CPL would equate to the "remaining duration" as it reflects the # of days between the status date and the forecast finish date

2) the TF would be the difference between the current forecast finish date and the baseline finish date

If the above is correct, then if

a) remaining duration is 100 days and the current finish date is 10 days later that the baseline date, the formula would be (100-10)/100 for a CPLI of .9

b) if the current finish date is 10 earlier than the baseline finish date, the formula would result in (100+10)/100 for a CPLI of 1.1

Dan

Tuesday, September 8, 2020 5:08 PM
• Yep, so CPLI=1.0 means the project is on schedule; CPLI>1 means the project is ahead of schedule; and CPLI<1 means the project is behind schedule.  I think in DCMA a value less than 0.95 gets an action request of some kind.

The root calculations actually used a version of total float determined with a late constraint on the baseline finish date.  Negative float says you are behind schedule.  CPLI<1 says the same thing, with an adjustment for how much time you have to make up the deficit....  It's not bad, just tedious.

Clarification: Be careful ever equating "Remaining Duration" in Project with CPL or anything else, especially for summary tasks.

• Edited by Wednesday, September 9, 2020 12:54 PM Clarification
Tuesday, September 8, 2020 10:53 PM