Planned Progress in MS Project: how to build the formula, step by step

Why this isn't built in

MS Project ships with % Complete, % Work Complete and Physical % Complete — all of which measure what has already happened. There is no native "Planned % Complete" column answering the question every project manager asks at the monthly meeting: "as of today, how much should we have finished?"

Without that column you cannot compute schedule variance in percentage terms, the most basic progress control indicator. The good news: Project already stores everything needed in the baseline; you just have to build a custom field that reads it.

This article covers five options, from simplest to most accurate, with full menu paths and every field to fill in.

Prerequisites: nothing works without these

Three settings must be right before any formula. If your column returns zero on every row, the cause is almost always here.

1. Baseline saved

Without a baseline, Baseline Start and Baseline Duration come back empty and the formula returns zero.

2. Status date set

Update this field at every measurement cycle. It defines the "today" of the calculation.

3. Weighting basis decided

Before choosing a formula, decide how progress is actually measured on your project:

• By duration — each task advances linearly in time. Simple, but assumes a 30-day task is 50% done on day 15, which is rarely true for work with heavy mobilization.
• By cost — progress follows planned spend. Reproduces the real S-curve and is the standard basis for contractual measurement.
• By work (man-hours) — progress follows planned effort. Best when labor dominates the scope.

Option 1 uses duration; Options 2 and 3 use cost and work.

Option 1 — Linear duration formula

Fastest to implement and adequate for most mid-sized schedules.

Full path

1. Project tab → Properties group → Custom Fields
2. At the top of the dialog:
   • Field: Task
   • Type: Number
3. In the list, select Number1
4. Rename... → type Planned % Complete → OK
5. Under Custom attributes, choose Formula... → build the formula below
6. Under Calculation for task and group summary rows, choose Use formula
7. Under Values to display, leave Data
8. OK → OK

The formula

IIf([Baseline Duration]=0,
    IIf([Baseline Cost]=0 And [Baseline Work]=0, 0,
        IIf([Status Date]>=[Baseline Finish], 100, 0)),
    IIf([Status Date]>=[Baseline Finish], 100,
    IIf([Status Date]<=[Baseline Start], 0,
    100*ProjDateDiff([Baseline Start],[Status Date])/[Baseline Duration])))

Branch by branch

Note that test order matters. Both boundary tests come before the division, which guarantees the result never exceeds 100 or goes negative, and that the division never runs with a zero denominator.

Why not use ProjDateValue("NA") to test for empty fields

It's the pattern most often found online, but it depends on the installation language ("NA" in English, "ND" in Portuguese) and fails silently when the abbreviation doesn't match. Testing [Baseline Duration]=0 is language-independent and covers the same case.

Two typing traps

Don't type field names. Inside the Formula dialog use the Field button → Date → Baseline Start. Field names are localized per installation language, so an English name typed into a localized install throws an error. Function names (IIf, ProjDateDiff) do not change.

Check the argument separator. English installs use commas; Portuguese and most European locales use semicolons. A formula rejected for no apparent reason is usually this.

Insert the column

Right-click any column header → Insert Column → type Planned % Complete.

Option 2 — Cost-weighted (BCWS) — the accurate one

Technically superior. Instead of assuming linear progress, it uses BCWS (Budgeted Cost of Work Scheduled), which Project already spreads over time according to actual resource assignments — meaning it reproduces the schedule's true S-curve.

Required setup first

The formula

Repeat steps 1–8 from Option 1 using Number2, renamed Planned % (Cost):

IIf([Baseline Cost]=0, 0, 100*[BCWS]/[Baseline Cost])

Insert [BCWS] with the Field button → Cost category → BCWS. On localized installations the label differs (it's COTA in Brazilian Portuguese) — pick it from that list rather than typing it from memory.

Why it's better

• Rolls up correctly. On summary rows both BCWS and Baseline Cost are exact sums of their subtasks, so whole-project planned progress is mathematically right.
• Respects real effort distribution. A task with slow early mobilization shows low BCWS in its first weeks, matching reality.
• It's the usual contractual basis. Physical-financial schedules are normally value-weighted.
• Handles milestones naturally. A milestone carrying cost enters by value; one without cost distorts nothing.

Requirement: the schedule must carry costs or assigned resources. A dates-only schedule has zero Baseline Cost and the formula returns zero — use Option 1 or 3 instead.

Option 3 — Work-weighted (man-hours)

There's a structural limitation worth knowing here: custom-field formulas cannot read timephased data. The BCWS field exists as cumulative cost through the status date, but there is no work equivalent. You therefore cannot write a formula for "budgeted work scheduled through the status date."

Two legitimate routes:

Route A — Uniform-rate proxy (formula only)

1. Assign every resource the same hourly rate (e.g. 1.00 currency unit/hour for all)
2. Save the baseline
3. Use the Option 2 formula

With a uniform rate, cost and work become proportional, so BCWS measures man-hour progress exactly.

Route B — VBA reading timephased work

The correct route when the schedule already carries real costs and you can't distort the rates. See Option 5, swapping the cost constant for the work constant.

Option 4 — No formula: the Earned Value table

If you only need the numbers once, with no custom fields:

Also worth inserting SPI (Schedule Performance Index) and SV (Schedule Variance): an SPI below 1.00 flags a delay with no formula at all.

Option 5 — VBA, when a formula isn't enough

Formulas have one hard limit: they cannot see timephased data. If you need planned progress at a date that is not the current status date — rebuilding a month-by-month S-curve, for instance — only VBA will do it.

Path: Developer tab → Visual Basic. If the tab is hidden: File → Options → Customize Ribbon → tick Developer. In the editor: Insert → Module, then paste the code.

Macro 1 — Planned progress at any date

Option Explicit

Sub PlannedProgressAtDate()
    Dim t As Task
    Dim tsv As TimeScaleValues
    Dim v As TimeScaleValue
    Dim sInput As String
    Dim dCutoff As Date
    Dim bcwsAccum As Double
    Dim baseTotal As Double

    sInput = InputBox("Cut-off date:", "Planned progress", Format(Date, "Short Date"))
    If Len(Trim(sInput)) = 0 Then Exit Sub
    If Not IsDate(sInput) Then
        MsgBox "Invalid date.", vbExclamation
        Exit Sub
    End If
    dCutoff = CDate(sInput)

    If dCutoff < ActiveProject.ProjectStart Then
        MsgBox "Planned progress on " & Format(dCutoff, "Short Date") & ": 0.0%", vbInformation
        Exit Sub
    End If

    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If Not t.Summary Then
                If t.BaselineCost > 0 Then
                    baseTotal = baseTotal + t.BaselineCost
                    Set tsv = t.TimeScaleData(ActiveProject.ProjectStart, dCutoff, _
                                              pjTaskTimescaledBaselineCost, pjTimescaleDays)
                    For Each v In tsv
                        If Not IsNull(v.Value) Then
                            If v.Value <> "" Then bcwsAccum = bcwsAccum + CDbl(v.Value)
                        End If
                    Next v
                End If
            End If
        End If
    Next t

    If baseTotal > 0 Then
        MsgBox "Planned progress on " & Format(dCutoff, "Short Date") & ": " & _
               Format(bcwsAccum / baseTotal, "0.0%"), vbInformation
    Else
        MsgBox "No baseline cost found. " & _
               "Save a baseline with resources or costs assigned.", vbExclamation
    End If
End Sub

To weight by work instead of cost, make two swaps:

• pjTaskTimescaledBaselineCost → pjTaskTimescaledBaselineWork
• t.BaselineCost → t.BaselineWork (both occurrences)

Macro 2 — Planned S-curve, month by month

Produces the cumulative series ready to paste into Excel. Output lands in the Immediate window (Ctrl+G in the VBA editor).

Option Explicit

Sub PlannedSCurveByMonth()
    Dim t As Task
    Dim tsv As TimeScaleValues
    Dim v As TimeScaleValue
    Dim period() As Double
    Dim i As Long, n As Long
    Dim baseTotal As Double, accum As Double
    Dim dStart As Date, dFinish As Date

    dStart = ActiveProject.ProjectStart
    dFinish = ActiveProject.ProjectFinish
    n = DateDiff("m", dStart, dFinish) + 2
    ReDim period(1 To n)

    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If Not t.Summary Then
                If t.BaselineCost > 0 Then
                    baseTotal = baseTotal + t.BaselineCost
                    Set tsv = t.TimeScaleData(dStart, dFinish, _
                                              pjTaskTimescaledBaselineCost, pjTimescaleMonths)
                    i = 0
                    For Each v In tsv
                        i = i + 1
                        If i <= n Then
                            If Not IsNull(v.Value) Then
                                If v.Value <> "" Then period(i) = period(i) + CDbl(v.Value)
                            End If
                        End If
                    Next v
                End If
            End If
        End If
    Next t

    If baseTotal <= 0 Then
        MsgBox "No baseline cost found.", vbExclamation
        Exit Sub
    End If

    Debug.Print "Month" & vbTab & "BCWS in month" & vbTab & "BCWS cumulative" & vbTab & "Planned %"
    accum = 0
    For i = 1 To n
        accum = accum + period(i)
        Debug.Print Format(DateAdd("m", i - 1, dStart), "mmm/yy") & vbTab & _
                    Format(period(i), "0.00") & vbTab & _
                    Format(accum, "0.00") & vbTab & _
                    Format(accum / baseTotal, "0.0%")
    Next i
    MsgBox "S-curve written to the Immediate window (Ctrl+G).", vbInformation
End Sub

One important detail: both macros pass the same date window (dStart, dFinish) for every task. This guarantees TimeScaleData returns the same number of periods per task, so they can be summed position by position.

Companion field: schedule variance in percentage points

Planned progress alone decides nothing. Create Number5 as Progress Variance (pp):

[% Complete]-[Number1]

Negative means behind schedule, in percentage points. Then, in the same Custom Fields dialog, click Graphical Indicators and configure the Nonsummary rows tab:

Order matters: Project evaluates tests top to bottom and stops at the first true one. Then, on the Summary rows tab, tick Summary rows inherit criteria from nonsummary rows so the traffic light climbs the WBS.

Common pitfalls

Validating your setup

1. Set the status date one day before project start → every row should read 0%.
2. Set it one day after baseline finish → every row should read 100%.
3. Set it exactly mid-baseline on a long, simple task → Option 1 should read ~50%.
4. Create a test milestone with a baseline and confirm it flips from 0% to 100% on the right date.
5. Compare project-level planned progress against your physical-financial S-curve. A large gap means the weighting you chose isn't the one your contract uses.

Menu paths reflect MS Project 2016 through the Microsoft Project Online Desktop Client. In versions earlier than 2010, Custom Fields lives under Tools → Customize → Fields. Field labels vary by installation language — always insert field names with the dialog's Field button rather than typing them.