Smartsheet Standard Schedule

Column Summary

Title	Use	Type	Phase	Tag
% Complete	Summation/Propagation	% Complete	Actual	complete_%
% Complete Last	Summation/Propagation	% Complete	System
Activity	Project Manager (Setup/Maintain)	Metadata	All
Activity ID	System (Background)	System	All
Actual [hrs]	Summation/Propagation	Actual Cost	Actual	actual_hours
Actual Distributed [hrs]	Summation/Propagation	Actual Cost	Actual	actual_distriuted_hours
Actual Finish Date	Project Team (Frontline Input)	Date/Logic	Actual	date_finish_actual
Actual Recorded [hrs]	Project Team (Frontline Input)	Actual Cost	Actual	actual_hours_recorded
Actual Start Date	Project Team (Frontline Input)	Date/Logic	Actual	date_start_actual
Actual Variance to Baseline Finish Date	Calculation	Metric	Actual vs Baseline	var_date_finish_actual_to_baseline
Actual Variance to Baseline Start Date	Calculation	Metric	Actual vs Baseline	var_date_start_actual_to_baseline
Allocation %	System (Background)	Resourcing	All	allocation_%
AvazaSection	Calculation	System	All	api_avaza_section
Baseline Finish Date	Project Controls (Controlled)	Date/Logic	Baseline	date_finish_baseline
Baseline Planned % Complete	Summation/Propagation	% Complete	Baseline	complete_%_baseline
Baseline Schedule % Complete	Calculation	% Complete	Baseline	complete_%_baseline_schedule
Baseline Start Date	Project Controls (Controlled)	Date/Logic	Baseline	date_start_baseline
Budget [hrs]	Summation/Propagation	Budget	Baseline	budget_hours
Budget [$]	Summation/Propagation	Budget	Baseline	budget_$
Budget Assigned [hrs]	Project Controls (Controlled)	Budget	Baseline	budget_hours_assigned
Budget Assigned [$]	Project Controls (Controlled)	Budget	Baseline	budget_$_assigned
Budget Assigned + Requested [hrs]	Calculation	Budget	Expected	budget_hours_assigned_and_requested
Budget Issues	System (Display&Filter)	Budget	All	budget_issues
Budget Requested [hrs]	Project Controls (Controlled)	Budget	Expected	budget_hours_requested
Budget Requested [$]	Project Controls (Controlled)	Budget	Expected	budget_$_requested
Calculated % Complete	Calculation	% Complete	Actual	complete_%_calculated
Check - Finish Date vs %-Complete	System (Display&Filter)	System	All
Check - Start Date vs %-Complete	System (Display&Filter)	System	All
Client Hidden?	System (Display&Filter)	Metadata	All
Control Conditional Formatting	System (Display&Filter)	System	All	control_cond_formatting
Cost Code	Project Controls (Controlled)	Cost Coding	All	cost_code
Cost Unit	Project Controls (Controlled)	Cost Coding	All	cost_unit_designation
Cost Unit Activity	System (Display&Filter)	Cost Coding	All	cost_unit_description
Cost Unit Issues	System (Display&Filter)	Cost Coding	All	cost_unit_issues
Cost Unit or Has Cost Unit Descendants	System (Background)	Cost Coding	All	cost_unit_or_has_descendants
CPI	Calculation	Metric	Actual vs Baseline	cpi
CV	Calculation	Metric	Actual vs Baseline	cv
CV%	Calculation	Metric	Actual vs Baseline	cv_%
Default FTC [hrs]	Calculation	Forecast Cost	Forecast (to Baseline)	ftc_default
Default FTC Including Change [hrs]	Calculation	Forecast Cost	Forecast (to Planned)	ftc_default_with_change
Descendant Cost Units	System (Background)	System	All	cost_unit_descendants
Discipline(s)	Project Manager (Setup/Maintain)	Metadata	All
Duration	Project Manager (Setup/Maintain)	Date/Logic	Expected
Effective Cost Unit	System (Background)	Cost Coding	All	cost_unit_effective
Expand/Collapse	System (Display&Filter)	Metadata	All
Expected Budget [hrs]	Summation/Propagation	Budget	Expected	budget_hours_with_change
EV [hrs]	Summation/Propagation	Earned Value	Actual vs Baseline	ev_hours
EV at Budget Level [hrs]	Calculation	Earned Value	Actual vs Baseline	ev_hours_budget_level
EVM Method	Project Manager (Setup/Maintain)	% Complete	All	complete_%_evm_method
FAC [hrs]	Summation/Propagation	Forecast Cost	Forecast (to Baseline)	fac_hours
FAC Including Change [hrs]	Summation/Propagation	Forecast Cost	Forecast (to Planned)	fac_hours_with_change
Filter - Client View	System (Display&Filter)	System	All
Filter - Expand/Collapse	System (Display&Filter)	System	All
Finish Date	Project Manager (Setup/Maintain)	Date/Logic	Expected	date_finish_expected
Forecast Finish Date	Project Team (Frontline Input)	Date/Logic	Forecast (to Baseline)	date_finish_fcast
Forecast Hours to Finish	Project Team (Frontline Input)	Forecast Cost	Forecast (to Baseline)	ftc_hours_entry
FTC [hrs]	Summation/Propagation	Forecast Cost	Forecast (to Baseline)	ftc_hours
FTC Including Change [hrs]	Summation/Propagation	Forecast Cost	Forecast (to Planned)	ftc_hours_with_change
FUTURE - Parent for Apportioned Effort	FUTURE	% Complete	Actual
Gated Progress Identifier	Project Manager (Setup/Maintain)	% Complete	Actual
Gated Progress Reference	Project Manager (Setup/Maintain)	% Complete	Actual
GTE PM	System (Background)	System	All
High Level Schedule(s)	Project Manager (Setup/Maintain)	Metadata	All
Information	Project Manager (Setup/Maintain)	Metadata	All
Issue Details	System (Display&Filter)	System	All	issue_details
Issue Flag	System (Display&Filter)	System	All	issue_flag
Last Progress Update Date	System (Background)	System	All
Manual % Complete	Project Team (Frontline Input)	% Complete	Actual	complete_%_manual
Manual Progress Responsible Person	Project Manager (Setup/Maintain)	Resourcing	All
Lumpsum Progress Payment [$]	Project Manager	Forecast Revenue	All
Modified By	System (Background)	System	All
Non-Standard Capacity	Project Manager (Setup/Maintain)	Resourcing	All	non_standard_capacity
Planned % Complete	Summation/Propagation	% Complete	Expected	complete_%_expected
Planned Variance to Baseline Finish Date	Calculation	Metric	Planned vs Baseline	variance_finish_expected_to_baseline
Planned Variance to Baseline Start Date	Calculation	Metric	Planned vs Baseline	variance_start_expected_to_baseline
Predecessors	Project Manager (Setup/Maintain)	Date/Logic	All	predecessors
Predecessors (helper)	System (Background)	System	All
Progress Health	System (Display&Filter)	System	All	progress_health
Project	System (Background)	System	All
Project ID	System (Background)	System	All
PV [hrs]	Summation/Propagation	Planned Value	Baseline	pv_hours
PV at Budget Level [hrs]	Calculation	Planned Value	Baseline	pv_hours_budget_level
PVx [hrs]	Summation/Propagation	Planned Value	Expected	pv_hours_with_change
PVx at Budget Level [hrs]	Calculation	Planned Value	Expected	pv_hours_with_change_budget_level
Reimbursable Activity	Project Controls (Controlled)	System	All	is_reimb_activity
Reimbursable?	Project Controls (Controlled)	Metadata	All	reimb_designation
Resource(s)	Project Manager (Setup/Maintain)	Resourcing	All	resources
Resource Loading	System (Background)	Resourcing	All	resource_loading
Resourcing Issues	System (Display&Filter)	Resourcing	All	resourcing_issues
Row ID	System (Background)	System	All	row_id
Schedule % Complete	Calculation	% Complete		complete_%_schedule
Schedule Status	System (Background)	System	All
Scheduling Issues	System (Display&Filter)	Scheduling	All	scheduling_issues
Sheet	System (Background)	System	All	sheet_name
SPI	Calculation	Metric	Actual vs Baseline	spi
Start Date	Project Manager (Setup/Maintain)	Date/Logic	Expected	date_start_expected
Status	System (Display&Filter)	System	All	status
Successors	Project Manager (Setup/Maintain)	Date/Logic	All	successors
SV	Calculation	Metric	Actual vs Baseline	sv
SV%	Calculation	Metric	Actual vs Baseline	sv_%
TCPI	Calculation	Metric	To-Complete Performance Index	tcpi
Variance	System (Background)	System	All	wbs
WBS	System (Background)	System	All	wbs
WBS Descendants	System (Background)	System	All	wbs_descendants
WBS Level	System (Background)	System	All	wbs_level

Column Details

% Complete

Summarizes up through the WBS the % Complete which is determined at lower activity levels (as complete_%_calculated).

Formula

=IF(
    PARENT([Calculated % Complete]@row) > 0, 
    PARENT([Calculated % Complete]@row),
    IF(
        [EV \[hrs\]]@row <> 0, 
        [EV \[hrs\]]@row / [Budget \[hrs\]]@row, 
        IF(
            AND([Budget \[hrs\]]@row <> 0, [Budget \[hrs\]]@row <> ""), 
            IFERROR( AVG(CHILDREN([Calculated % Complete]@row) ), ""), 
            [Calculated % Complete]@row
        )
    )
)

% Complete Last

System column. Used to store % Complete for comparison purposes between days.

Activity

The work item or summary header.

Activity ID

System autonumber column; used for interlinking of the tasks between Smartsheet and Avaza (when migrating actuals booked in Avaza to the schedule).

Actual [hrs]

Summarizes up through the WBS the Actual Cost which is entered at the Cost Unit level (captured in the Cost System and input to the Project Schedule as actual_hours_recorded).

Formula

=IF(
    AND([Actual Recorded \[hrs\]]@row <> "", [Actual Recorded \[hrs\]]@row <> 0), 
    [Actual Recorded \[hrs\]]@row, 
    SUM( CHILDREN([Actual \[hrs\]]@row) )
)

Actual Distributed [hrs]

Actual hours, as read from the Avaza for parent activity, distributed across all its children (using activity's EV). It helps to correctly calculate FTC for non Cost Unit activities (and subsequently the Resource Loading).

Formula

=IFERROR(
    IF(
        [Effective Cost Unit]@row = PARENT([Effective Cost Unit]@row),
        PARENT() / PARENT([EV \[hrs\]]@row) * [EV \[hrs\]]@row,
        IF(
            [Cost Unit]@row = 1,
            [Actual Recorded \[hrs\]]@row,
            0
        )
    ),
    0
)

Actual Finish Date

Represents the date of completion of an activity, where this has occurred, and is equal to the date_finish_planned at that point. It is automatically entered by the GTE Portal.

Actual Recorded [hrs]

Actual Cost in hours as captured in the Cost System (Avaza) and automatically synchronized to the Project Schedule (by GTE Project Portal).

Actual Start Date

Represents the actual date of commencement of an activity, where this has occurred, and is equal to the date_start_expected at that point. It is automatically entered by the GTE Portal.

Actual Variance to Baseline Finish Date

Compares the Actual Finish Date (date_finish_actual with the Baseline Finish Date (date_finish_baseline. Negative variance indicates a slip against the baseline.

Formula

=IF(
    AND([Baseline Finish Date]@row <> "", [Actual Finish Date]@row <> ""), 
    NETWORKDAYS([Actual Finish Date]@row, [Baseline Finish Date]@row) + 1, 
    ""
)

Actual Variance to Baseline Start Date

Compares the Actual Start Date (date_start_actual with the Baseline Start Date (date_start_baseline. Negative variance indicates a slip against the baseline.

Formula

=IF(
    AND([Baseline Start Date]@row <> "", [Actual Start Date]@row <> ""), 
    NETWORKDAYS([Actual Start Date]@row, [Baseline Start Date]@row) + 1, 
    ""
)

Allocation %

System column used by the Resource Management as an input into an overall resource management calculations. For the reason it is used by a smartsheet system, it is automatically capped to 100% as well as displayed following system conditional formatting and therefore is not suitable to indicate the actual resource loading information. The resource_loading column is used for this purpose, of which Allocation % is a direct copy..

Formula

=[Resource Loading]@row

AvazaSection

System column for use with the API integration with Avaza.

Formula

=IF(
    [WBS Level]@row <= 2, Activity@row, 
    INDEX( ANCESTORS(Activity@row), 2 )
)

Baseline Finish Date

The finish date for the activity as agreed with the client either explicitly in the Baseline, or implicitly to support explicit commitments in the Baseline. The column is controlled by Smartsheet and will only be updated by a re-baseline in the user interface. Note that the Smartsheet Baseline functionality is not used directly due to various limitations.

Baseline Planned % Complete

Determines Baseline Planned % Complete at all levels up through the WBS using the Planned Values (calculated at the lowest possibly determinable level as pv_hours_budget_level and summarized upwards as pv_hours) and summarized Budgets (budget_hours).

Note

An activity and summary that does not have a budget (budget_hours), ie either directly assigned or from it's children, will not have Planned Value. As these may still be considered progressable, the complete_%_baseline_schedule is used as a fallback planned % for those activities or summaries, however the percentage does not impact higher level summaries.

Formula

=IF(
    [Budget \[hrs\]]@row <> 0, 
    [PV \[hrs\]]@row / [Budget \[hrs\]]@row, 
    [Baseline Schedule % Complete]@row
)

Baseline Schedule % Complete

Determines the Schedule % Complete for an activity (or summary) at the data date (today) as a linearization of progress from the Baseline Start Date to the Baseline Finish Date, factoring in non-working days in the project calendar.

Note

The values yielded for summaries in the WBS are not accurate portrayals of Planned Value as these linearize at those summary levels only. Accurate "Planned %" is given by comp%_baseline_planned_all.

Formula

=IF(
    AND([Baseline Start Date]@row <> "", [Baseline Finish Date]@row <> ""),
    ROUND(
        IF(
            TODAY() < [Baseline Start Date]@row,
            0,
            IF(
                TODAY() >= [Baseline Finish Date]@row,
                1,
                NETWORKDAYS(
                    [Baseline Start Date]@row,
                    TODAY()
                ) / NETWORKDAYS( [Baseline Start Date]@row, [Baseline Finish Date]@row )
            )
        ),
    2),
    0
)

Baseline Start Date

The start date for the activity as agreed with the client either explicitly in the Baseline, or implicitly to support explicit commitments in the Baseline. The column is controlled by Smartsheet and will only be updated by a re-baseline in the user interface. Note that the Smartsheet Baseline functionality is not used directly due to various limitations.

Budget [hrs]

Summarizes up through the WBS the Budgeted Cost, in hours, which is entered at various budgeted levels (captured in the Baseline and input to the schedule as budget_hours_assigned.

Formula

=IF(
    AND([Budget Assigned [hrs]]@row <> "", [Budget Assigned [hrs]]@row <> 0), 
    [Budget Assigned [hrs]]@row, 
    SUM(CHILDREN())
)

Budget [$]

Summarizes up through the WBS the Budgeted Cost, in dollars, which is entered at various budgeted levels (captured in the Baseline and input to the schedule as budget_$_assigned.

Formula

=IF(
    AND([Budget Assigned [$]]@row <> "", [Budget Assigned [$]]@row <> 0), 
    [Budget Assigned [$]]@row, 
    SUM(CHILDREN())
)

Budget Assigned [hrs]

Budgeted Cost, in hours, as assigned to relevant individual activities. The sum of the assigned budgets equals the total project Budgeted Cost, and is summarised up through the WBS as budget_hours.

Budget Assigned [$]

Budgeted Cost, in dollars, as assigned to relevant individual activities. The sum of the assigned budgets equals the total project Budgeted Cost, and is summarised up through the WBS as budget_$.

Budget Assigned + Requested [hrs]

Represents the approved Budgeted Cost, in hours, for the activity (budget_hours_assigned) plus any additional hours that have been requested formally but not yet been approved (budget_hours_requested).

Formula

=[Budget Assigned [hrs]]@row + [Budget Requested [hrs]]@row

Budget Issues

Used to assist in assigning Budget using the budget_hours_assigned column, providing indication where overlapping budgets have been assigned, ie where assignments have been made on ancestors to an activity that has also been directly assigned a budget. Issues in this column will be represented by a symbol in the flag column if the Sheet Summary setting SETTING - Show Budget Issues is checked.

Formula

=IF(
    AND( [Budget Assigned \[hrs\]]@row <> 0, [Budget Assigned \[hrs\]]@row <> " ),
    IF(
        SUM( CHILDREN([Budget Assigned \[hrs\]]@row) ) > 0,
        "Budget also assigned on children"",
        ""
    ),
    ""
)

Budget Requested [hrs]

Budgeted additional (unapproved and so not included in the Baseline) Cost, in hours, as assigned to relevant individual activities. Represents the sum of all additional hours requested for the activity (through formal change management with the client) that has not yet been approved and added to the (Baseline) Budget (budget_hours_assigned) for the activity.

Formula

= SUM(
    COLLECT(
        {Project Change Register - Activity Labour (hrs)}, 
        {Project Change Register - Existing Activity ID}, [Activity ID]@row, 
        {Project Change Register - Variation Status for Act}, "Submitted"
    )
)

Budget Requested [$]

Budgeted additional (unapproved and so not included in the Baseline) Cost, in dollars, as assigned to relevant individual activities. Represents the sum of all additional dollar costs requested for the activity (through formal change management with the client) that has not yet been approved and added to the (Baseline) Budget for the activity.

Formula

= SUM(
    COLLECT(
        {Project Change Register - Applicable Activity Total ($)}, 
        {Project Change Register - Existing Activity ID}, [Activity ID]@row, 
        {Project Change Register - Variation Status for Act}, "Submitted"
    )
)

Calculated % Complete

Calculates the % Complete to use for the activity dependant on the complete_%_evm_method selected.

Formula

=IF(
    [EVM Method]@row = "Manual % Complete", 
    [Manual % Complete]@row, 
    IF(
        [EVM Method]@row = "Linearised LoE", 
        [Planned % Complete]@row, 
        IF(
            [EVM Method]@row = "Gated", 
            ROUND(
                INDEX({GatedProgressSummary - % Complete},
                  MATCH([Gated Progress Reference]@row,
                        {GatedProgressSummary - Reference},
                        0)
                ),
                2
            ), 
            IF(
                [EVM Method]@row = "Apportioned Effort", 
                "NOT SUPPORTED", 
                [Manual % Complete]@row
            )
        )
    )
)

Check - Finish Date vs %-Complete

Returns 'Error' when the date_finish_expected disagrees with the complete_%. The value is used to highlight both fields as requiring attention.

Formula

=IF(
    OR(
        AND( [Finish Date]@row <> "", [Finish Date]@row > TODAY(), [% Complete]@row = 1 ),
        AND( [Finish Date]@row <> "", [Finish Date]@row < TODAY(), [% Complete]@row <> 1 )
    ),
    "Error",
    "OK"
)

Check - Start Date vs %-Complete

Returns 'Error' when the date_start_expected disagrees with the complete_%. The value is used to highlight both fields as requiring attention.

Formula

=IF(
    OR(
        AND( [Start Date]@row <> "", [Start Date]@row < TODAY(), [% Complete]@row = 0 ),
        AND( [Start Date]@row <> "", [Start Date]@row > TODAY(), [% Complete]@row <> 0 )
    ),
    "Error",
    "OK"
)

Client Hidden?

Checking this column will allow the row to be hidden when applying the Filter - Client Hidden filter.

Control Conditional Formatting

System column.

Formula

=IF(
    [SETTING - Obscure System Columns]# = 1,
    "Obscure System Columns", 
    ""
)

Cost Code

Entry column to define a Cost Unit (by assignment of Cost Code) where costs will be captured in the Cost System. Alternatively, the Cost Code can be assigned automatically based on the Cost Unit designations made by checking the cost_unit_designation column. Automatic assignment is the recommended option for a number of reasons and manual Cost Code assignment should not be done without consulting Project Controls.

Cost Unit

Optional column to designate a Cost Unit without assigning a Cost Code manually. The GTE Project Portal can then be used to auto-assign the Cost Codes to the designated Cost Units. The assigned Cost Codes will be populated to the cost_code column.

Cost Unit Activity

Combines the cost_unit_effective and the activity into a string.

Formula

=IF(
    [Cost Code]@row <> "", 
    [Cost Code]@row + " | " + Activity@row, 
    Activity@row
)

Cost Unit Issues

Used to assist in designating Cost Units using the cost_unit column, providing details on any designation issues such as overlapping Cost Unit designations or budgeted activities not covered by a Cost Unit designation. Issues in this column will be represented by a symbol in the flag column if the Sheet Summary setting SETTING - Show Cost Unit Issues is checked.

Formula

=IF(
    [Budget \[hrs\]]@row = 0, 
    "", 
    IF(
        [Cost Unit]@row = 0, 
        IF(
            COUNTIF(ANCESTORS([Cost Unit]@row), 1) > 1, 
            "Multiple ancestor cost unit designations exist", 
            IF(
                AND(
                    COUNTIF(ANCESTORS([Cost Unit]@row), 1) = 0, 
                    [WBS Level]@row <> 1, 
                    OR(
                        COUNT(CHILDREN([Cost Unit Issues]@row)) > 0, 
                        COUNT(CHILDREN(Activity@row)) = 0
                    )
                ), 
                "Not cost unit designated, and no applicable ancestor cost units or successor designations to cover all successors", 
                ""
            )
        ), 
        IF(
            [WBS Level]@row = 1, 
            "Cost unit designation cannot occur at WBS level 1. If a single cost unit is required, assign all budget to a level 2 delivery element and designate this as the cost unit", 
            IF(
                COUNTIF(ANCESTORS([Cost Unit]@row), 1) > 0, 
                "Cost unit designated and ancestor cost unit designation(s) also exist", 
                ""
            )
        )
    )
)

Cost Unit or Has Cost Unit Descendants

System column.

Formula

=IF(
    OR(
        [Cost Code]@row <> "",
        COUNT(DESCENDANTS([Cost Code]@row)) > 0
    ),
    1,
    0
)

CPI

Cost Performance Index (based on hours).

Formula

=IFERROR(
    [EV \[hrs\]]@row / [Actual \[hrs\]]@row, 
    ""
)

CV

Cost Variance (in hours).

Formula

=[EV \[hrs\]]@row - [Actual \[hrs\]]@row

CV%

Cost Variance (in hours), expressed as a percentage of Planned Value (hours).

Formula

=IFERROR( 
    CV@row / [PV \[hrs\]]@row, 
    " 
)

Default FTC [hrs]

Calculates a default value to be used if a Forecast Hours to Finish (ftc_hours_entry) is not entered and there is assigned budget for the activity. If there is no assigned budget, the sum of the child activities is used. The value calculated is generally the full assigned budget (budget_hours_assigned) as a conservative estimate, however for an EVM Method (complete_%_evm_method) of "Linearised LoE" the actual hours to date (actual_hours_recorded) will be deducted as these activities are expected to meet the budget.

Formula

=IF(
    [WBS Descendants]@row = 0,
    IF(
        [% Complete]@row = 1,
        0,
        IF(
            AND(
                [Budget \[hrs\]]@row <> "",
                [Budget \[hrs\]]@row <> 0,
                [Resource(s)]@row <> ""
            ),
            IF(
                [EVM Method]@row = "Linearised LoE",
                [Budget \[hrs\]]@row - [Actual Distributed \[hrs\]]@row,
                IF(
                    AND(
                        [Assume Linear Performance]# = 1, 
                        [Actual Distributed \[hrs\]]@row > 0
                    ),
                    IF(
                        [% Complete]@row > 0,
                        [Actual Distributed \[hrs\]]@row * (1 - [% Complete]@row) / [% Complete]@row,
                        [Budget \[hrs\]]@row
                    ),
                    [Budget \[hrs\]]@row - [Actual Distributed \[hrs\]]@row
                )
            ),
            0
        )
    ),
    SUM(CHILDREN())
)

Default FTC Including Change [hrs]

Calculates a default value to be used if a Forecast Hours to Finish (ftc_hours_entry) is not entered and there is assigned budget and/or requested hours (through formal change management with the client) for the activity. If there is no assigned or requested hours for the activity, the sum of the child activities is used. The value calculated is generally the full assigned and requested budget (budget_hours_assigned_and_requested) as a conservative estimate, however for an EVM Method (complete_%_evm_method) of "Linearised LoE" the actual hours to date (actual_hours_recorded) will be deducted.

Formula

 =IF(
    [% Complete]@row = 1, 
    0, 
    IF(
        AND( [Expected Budget \[hrs\]]@row <> "", [Expected Budget \[hrs\]]@row <> 0 ), 
        IF(
            [EVM Method]@row = "Linearised LoE", 
            [Expected Budget \[hrs\]]@row - [Actual \[hrs\]]@row, 
            IF(
                AND( [Assume Linear Performance]# = 1, [% Complete]@row > 0, [Cost Unit]@row = 1 ), 
                [Actual \[hrs\]]@row * (1 - [% Complete]@row) / [% Complete]@row, 
                [Expected Budget \[hrs\]]@row
            )
        ), 
        SUM(CHILDREN())
    )
)    

Descendant Cost Units

System column.

Formula

=COUNTIF( DESCENDANTS([Cost Unit]@row), 1 )

Discipline(s)

Optional column. Allows the discipline or disciplines involved with the activity row to be defined for possible filtering or reporting uses.

Duration

The working days between, and including, the Start (date_start_planned) and Finish Dates (date_finish_planned) of the activity; where working days are as defined by the project calendar (Smartsheet setting).

Effective Cost Unit

Propagates a Cost Code entered at the Cost Unit level down to descendant levels in the WBS for explicitly clarifying where the costs should be captured in the Cost System.

Formula

=IF(
    PARENT() <> "", 
    PARENT(), 
    [Cost Code]@row
)

Expand/Collapse

Checking this column will mean the row and its parents are visible when applying the Filter - Expand/Collapse filter. This is useful to maintain a certain schedule view that hides unnecessary detail.

Expected Budget [hrs]

Summarizes hours up through the WBS as for budget_hours but includes additional hours that may have been requested of the client through formal change management, but not yet approved. The additional hours are input at the budgeted level as budget_hours_requested.

Formula

=IF(
   AND(
       [Budget Assigned + Requested [hrs]]@row <> "", 
       [Budget Assigned + Requested [hrs]]@row <> 0
    ), 
    [Budget Assigned + Requested [hrs]]@row, 
    SUM(CHILDREN())
)

EV [hrs]

Summarizes up through the WBS the Earned Value calculated at the lowest possibly determinable level (as ev_budget_level). The lowest possibly determinable level is where both % Complete and Budget Cost, in hours, exist.

Formula

=IF(
    AND([EV at Budget Level \[hrs\]]@row = 0, [WBS Descendants]@row > 0), 
    IF(
        SUM( CHILDREN([EV \[hrs\]]@row) ) > 0, 
        SUM( CHILDREN([EV \[hrs\]]@row) ), 
        IFERROR(
            AVG( CHILDREN([Calculated % Complete]@row)) 
                * IF(
                    SUM( CHILDREN([Budget Assigned \[hrs\]]@row) ) <> 0, 
                    SUM(CHILDREN([Budget Assigned \[hrs\]]@row)), 
                    [Budget \[hrs\]]@row
                ), 
            0
        )
    ), 
    [EV at Budget Level \[hrs\]]@row
)

EV at Budget Level [hrs]

Earned Value (in hours) at the budgeted activity level. Where an activity is budgeted, this is the Earned Value (in hours) for that activity as the budget multiplied by the physical % complete complete_%_calculated. The duration of the activity is determined from the Baseline Start and Finish dates (as complete_%_baseline_schedule), using date_start_baseline and date_finish_baseline, and the budget used only includes the approved (Baseline) budget (budget_hours_assigned).

Formula

=IF(
    SUM(CHILDREN([EV at Budget Level \[hrs\]]@row)) > 0, 
    0, 
    [Calculated % Complete]@row * [Budget \[hrs\]]@row
)

EVM Method

The default method for determing an activity's % Complete (complete_%) is using the manual entry column (complete_%_manual). This column is used to select an alternate method such as "Linearised LoE", "Gated" or "Apportioned Effort".

FAC [hrs]

Forecast at Complete (in hours). This is simply the hours incurred to date (actual_hours) plus the forecast hours to complete (ftc_hours).

Formula

=[Actual \[hrs\]]@row + [FTC \[hrs\]]@row

FAC Including Change [hrs]

Forecast at Complete (in hours), factoring expected changes to the budget. This is simply the hours incurred to date (actual_hours) plus the forecast hours to complete, where in this instance this includes change (ftc_default_with_change).

Formula

=[Actual \[hrs\]]@row + [FTC Including Change \[hrs\]]@row

Filter - Client View

System column.

Formula

=IF(
    OR(
        [Client Hidden?]@row = 1,
        PARENT() = "Hidden"
    ),
    "Hidden",
    "Visible"
)

Filter - Expand/Collapse

System column.

Formula

=IF(
    OR(
        [Expand/Collapse]@row = 1,
        COUNTIF(DESCENDANTS(), "Visible"
    ),
    "Visible",
    "Hidden"
)

Finish Date

The Finish Date for the activity as most recently planned. This may differ from the Baseline equivalent (date_finish_baseline) and may or may not reflect field feedback (date_finish_fcast) depending on whether the Project Manager intends to hold or measure the project team to earlier plans. When an activity is complete, the Finish Date should equal the Actual Finish Date (date_finish_actual).

Issue Details

Combines all types of issues into one text. The aim is to have one concise information that describes the reason for the issue_flag is indicating issue(s).

Formula

=IF(
    [Cost Unit Issues]@row <> "", 
    "COST UNIT: " + [Cost Unit Issues]@row + " || ", 
    ""
) +
IF(
    [Budget Issues]@row <> "",
    "BUDGET: " + [Budget Issues]@row + " || ", 
    ""
) + 
IF(
    [Resourcing Issues]@row <> "", 
    "RESOURCING: " + [Resourcing Issues]@row + " || ", 
    ""
) + 
IF(
    [Scheduling Issues]@row <> "", 
    "SCHEDULING: " + [Scheduling Issues]@row, 
    ""
)

Issue Flag

A cross (red) indicates an issue on the row. An exclamation (yellow) indicates an issue on a descendant row. The issues can be to do with Budgeting, Cost Coding, or Resourcing and the flags responsiveness to each is dependent on the selected options in the relevant 'Sheet Summary' settings. The specifics of the issues are detailed in the budget_issues, cost_unit_issues, resourcing_issues, and/or scheduling_issues columns as relevant.

Formula

=IF(
    COUNT(
        IF([SETTING - Show Budget Issues]#, [Budget Issues]@row), 
        IF([SETTING - Show Cost Unit Issues]#, [Cost Unit Issues]@row), 
        IF([SETTING - Show Resourcing Issues]#, [Resourcing Issues]@row),
        IF([SETTING - Show Scheduling Issues]#, [Scheduling Issues]@row)
    ) > 0, 
    "No", 
    IF(
        COUNT(
            IF([SETTING - Show Budget Issues]#, DESCENDANTS([Budget Issues]@row)), 
            IF([SETTING - Show Cost Unit Issues]#, DESCENDANTS([Cost Unit Issues]@row)), 
            IF([SETTING - Show Resourcing Issues]#, DESCENDANTS([Resourcing Issues]@row)),
            IF([SETTING - Show Scheduling Issues]#, [Scheduling Issues]@row))
        ) > 0, 
        "Hold", 
        ""
    )
)   

Forecast Finish Date

The date, forecast by the relevant team member, when it is expected that the activity will be completed. This date is the input for the Project Manager's update of the (Planned) Start and Finish dates (date_start_expected and date_finish_expected), although the Project Manager will not necessarily change these dates based on the feedback.

Forecast Hours to Finish

The hours, forecast by the relevant team member, required to finish the activity as considered on the basis of the work completed at the date of entry. The forecast should be reflective of the remaining work, and should not factor budget and/or hours burned to date.

FTC [hrs]

Forecast to Complete (in hours). Where a forecast from the frontline is applicable for an activity (generally, where that activity is a Cost Unit), this value will be the frontline's forecast (provided as ftc_hours_entry). Where it has not been entered, and FTCs at the lower level do exist, the sum of these will be used. Failing that, a default value (ftc_default) will be used.

Formula

=IF(
    SUM(CHILDREN()) > 0, 
    SUM(CHILDREN()), 
    IF(
        [Forecast Hours to Finish]@row <> "", 
        [Forecast Hours to Finish]@row, 
        [Default FTC \[hrs\]]@row
    )
)

FTC Including Change [hrs]

Forecast to Complete (in hours), factoring expected changes to the budget. Where a forecast from the field is applicable for an activity, generally where that activity is a Cost Unit, this value will be the frontline's forecast (provided as ftc_hours_entry). Where it has not been entered, and FTCs Including Change at the lower level do exist, the sum of these will be used. Failing that, a default value (ftc_default_with_change) will be used.

Formula

=IF(
    SUM(CHILDREN()) > 0, 
    SUM(CHILDREN()), 
    IF(
        [Forecast Hours to Finish]@row <> "", 
        [Forecast Hours to Finish]@row, 
        [Default FTC Including Change \[hrs\]]@row
    )
)

FUTURE - Parent for Apportioned Effort

Future - disregard.

Gated Progress Identifier

Identifies the gated reference using the full path name from the original gated reference sheet hierarchy. This helps user by providing more readable descriptioin of the used gated reference thus simplifying the process of creating a gated reference structure.

Formula

=INDEX({GatedProgressSummary - Identifier}, MATCH([Gated Progress Reference]@row, {GatedProgressSummary - Reference}, 0))

Gated Progress Reference

Where "Gated" is selected as the earned value measurement method (complete_%_evm_method), this reference should be set to an external progress source (gated progress sheet).

GTE PM

System column. References GTE PM value in the Project Data aggregator sheet.

Formula

=INDEX({Project Data - GTE PM}, 1)

High Level Schedule(s)

Optional column. Selecting one or more of the existing options, such as 'Comms Site Works', or entering any new custom value allows this column to be used in global report filters to produce high level resourcing views.

Information

Free text entry to provide any comments against the activity that should be more visible than row comments. In some instances, this column may be populated from the original estimate and provide detail on the basis of the estimate for example.

Last Progress Update Date

System column. Automation records current date whenever manual % complete is changed. It is then used to evaluate when was the latest schedule progress updated whcih can then be indicated to the client as an information how latest the schedule progress is.

Lumpsum Progress Payment [$]

Value entered manually by Project Manager. It is applicable to lumpsum projects only and it indicates the progress (milestone) payments plan. The duration must be 0 (i.e. the "activity" has to be a milestone). The activity name should indicate the short description of the progress payment (e.g. "25% After FAT"). Column 'Information' can be used to enter more details. The values in this column are used for reporting on a Client's dashboard.

Manual % Complete

Where "Manual % Complete" is selected as the earned value measurement method (complete_%_evm_method), or where no selection is made, the physical % Complete of relevant activities should be entered in this column by project team members responsible for the work.

Manual Progress Responsible Person

Indicates person responsible for updating % Complete for the activity. It is then used for grouping in Progress-Manual report. It is a cell formula reading single value from Resource(s) column but can be replaced by any different contact.

Formula (cell)

=IF(
    CONTAINS(",", [Resource(s)]@row), 
    LEFT([Resource(s)]@row, FIND(",", [Resource(s)]@row)), 
    [Resource(s)]@row
)

Modified By

System column.

Non-Standard Capacity

Standard resource capacity is 8hrs/day. Set this value where the activity is, for example, performed on site and involves 12hr days not standard 8hr office days. In this example case, type '12'.

Planned % Complete

Determines Planned % Complete at all levels up through the WBS using the expected Planned Values (hours), ie budgets including additional requests (calculated at the lowest possibly determinable level as pv_hours_with_change_budget_level and summarized upwards as pv_hours_with_change) and summarized Budgets (budget_hours_with_change).

Note

An activity and summary that does not have a budget (budget_hours_with_change), ie either directly assigned or from it's children, will not have Planned Value. As these may still be considered progressable, the complete_%_schedule is used as a fallback planned % for those activities or summaries, however the percentage does not impact higher level summaries.

Formula

=IF(
    [Expected Budget \[hrs\]]@row <> 0, 
    [PVx \[hrs\]]@row / [Expected Budget \[hrs\]]@row, 
    [Schedule % Complete]@row
)

Planned Variance to Baseline Finish Date

Compares the (Planned) Finish Date (date_finish_expected) with the Baseline Finish Date (date_finish_baseline). Negative variance indicates a slip against the baseline.

Formula

=IF(
    AND([Baseline Finish Date]@row <> "", [Finish Date]@row <> ""), 
    NETWORKDAYS([Finish Date]@row, [Baseline Finish Date]@row) - 1, 
    ""
)

Planned Variance to Baseline Start Date

Compares the (Planned) Start Date (date_start_expected) with the Baseline Start Date (date_start_baseline). Negative variance indicates a slip against the baseline.

Formula

=IF(
    AND([Baseline Start Date]@row <> "", [Start Date]@row <> ""), 
    NETWORKDAYS([Start Date]@row, [Baseline Start Date]@row) - 1, 
    ""
)

Predecessors

Used to define relationships between activities as schedule logic.

Predecessors (Helper)

System column, used in evaluating the Successors column.

Formula

="|" + 
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(Predecessors@row, ",", "|"),
                "S", "|"),
            "F", "|"),
        " ", "")
    + "|"

Progress Health

With the use of traffic lights (red/yellow/orange), it indicates how is the activity performing when compared to the actual schedule. When red, activity completion is overdue, when orange the progress is lagging behind the plan and when green progress is as planned or better. For parent activity, this shows the worst value of all its descendants.

Formula

=IF(
    AND(
        Duration@row <> 0, 
        [Budget \[hrs\]]@row <> 0
    ), 
    IF(
        [WBS Descendants]@row > 0, 
        IF(
            COUNTIF(CHILDREN(), "Red") > 0,
            "Red", 
            IF(
                COUNTIF(CHILDREN(), "Yellow") > 0, 
                "Yellow", 
                "Green"
            )
        ), 
        IF(
            AND(
                [% Complete]@row <> 1, 
                [Finish Date]@row < TODAY()
            ), 
            "Red", 
            IF(
                IF(ISBLANK([% Complete]@row), 0, [% Complete]@row) < ([Schedule % Complete]@row - 0.025), 
                "Yellow", 
                "Green"
            )
        )
    )
)

Project

System mirror of Sheet Summary value.

Formula

=IFERROR("P" + [Project ID]# + " - " + [Project Name]#, "Enter Project ID in Sheet Summary")

Project ID

System mirror of Sheet Summary value.

Formula

=[Project ID]#

PV [hrs]

Summarizes up through the WBS the Planned Value (in hours) calculated at the budgeted (or lowest possibly "determinable") level (as pv_budget_level).

Formula

=IF(
    AND([PV at Budget Level \[hrs\]]@row <> "", [PV at Budget Level \[hrs\]]@row <> 0), 
    [PV at Budget Level \[hrs\]]@row, 
    SUM(CHILDREN())
)

PV at Budget Level [hrs]

Planned Value (in hours) at the budgeted activity level. Where an activity is budgeted, this is the Planned Value (in hours) for that activity as the budget multiplied by the percentage of time through the duration of the activity. The duration of the activity is determined from the Baseline Start and Finish dates (date_start_baseline and date_finish_baseline) as complete_%_baseline_schedule, and the budget used only includes the approved (Baseline) budget (budget_hours_assigned).

Formula

=IF(
    AND([Budget Assigned \[hrs\]]@row <> "", [Budget Assigned \[hrs\]]@row <> 0), 
    [Baseline Schedule % Complete]@row * [Budget Assigned \[hrs\]]@row, 
    0
)

PVx [hrs]

Summarizes up through the WBS the Planned Value Expected (in hours) calculated at the budgeted (or lowest possibly "determinable") level as pv_hours_with_change_budget_level, where this includes additional requested budget as well as approved/assigned budget (budget_hours_assigned_and_requested).

Formula

=IF(
    AND([PVx at Budget Level \[hrs\]]@row <> "", [PVx at Budget Level \[hrs\]]@row <> 0), 
    [PVx at Budget Level \[hrs\]]@row, 
    SUM( CHILDREN() )
)

PVx at Budget Level [hrs]

Planned Value Expected (in hours) at the budgeted activity level. Where an activity is budgeted, this is the Planned Value (in hours) for that activity as that budget multiplied by the percentage of time through the duration of the activity. "Expected" means that in this instance the duration of the activity is determined from the Start and Finish dates from the current plan (as complete_%_schedule), using date_start_expected and date_finish_expected), and the budget used is actually the approved/assigned budget plus any additional requested budget (budget_hours_assigned_and_requested).

Formula

=IF(
    OR(
        AND(
            [Budget Assigned \[hrs\]]@row <> "",
            [Budget Assigned \[hrs\]]@row <> 0
        ),
        AND(
            [Budget Requested \[hrs\]]@row <> "",
            [Budget Requested \[hrs\]]@row <> 0
        )
    ),
    [Schedule % Complete]@row * [Budget Assigned + Requested \[hrs\]]@row,
    0
)

Reimbursable Activity

Propagates the assignment of reimbursable activities, entered as a higher level designation (using reimb_designation) down to descendant levels in the WBS.

Formula

=IF(
    OR(
        [Reimbursable?]@row = 1, 
        PARENT() = 1
    ), 
    1, 
    0
)

Reimbursable?

For projects that include both lump sum and reimbursable (rates based) components, checking this column will designate the reimbursable items in the schedule. The designation will apply to all descendants of any checked item as determined by is_reimb_activity.

Resource(s)

Used to assign one or more human resources to budgeted activities. The assigned resource(s) share the budget requirement equally, linearized over the activity duration, for the purpose of assessing resource loading.

Resource Loading

Calculates the % of a single resource's capacity required to complete a budgeted activity, where this is a linear distribution of the remaining work over the remaining duration of the activity, divided by the number of resources and the daily capacity in hours of those resources (assumed to be 8hrs unless otherwise set using non_standard_capacity). If the activity is completely planned in the past, issue is flagged. This is achieved by the fact that remaining effort to complete the activity is resulted in negative number and so final percentage of Resource Loading is also a negative number and thus evaluated as error by resourcing_issues formula.

Formula

=IFERROR(
    ( [FTC \[hrs\]]@row
        / IF([Finish Date]@row >= TODAY(),
            NETWORKDAYS(
                IF(
                    [Start Date]@row < TODAY(),
                    TODAY(),
                    [Start Date]@row
                ),
                [Finish Date]@row
            ),
            IF([FTC \[hrs\]]@row = 0, 1, 0)
        )
    )
        / ( COUNTM([Resource(s)]@row)
            * IF(
                [Non-Standard Capacity]@row <> "",
                [Non-Standard Capacity]@row,
                8)
        ),
    ""
)

Resourcing Issues

Used to assist in designating human resource(s) using the resources column, providing details on any wrong/missing assignments or overloaded resources, and where an activity duration has not been properly established to allow the resource loading to be determined. Issues in this column will be represented by a symbol in the flag column if the Sheet Summary setting SETTING - Show Resourcing Issues is checked.

Formula

=IF(
    [% Complete]@row <> 1,
    IF(
        AND(
            COUNTM([Resource(s)]@row) <> 0,
            [Budget Assigned \[hrs\]]@row <> 0
        ),
        IF(
            Duration@row <> 0,
            IF(
                FIND("RES ", [Resource(s)]@row) <> 0,
                "Generic (RES xxxx) resource used",
                IF(
                    [Resource Loading]@row > 1,
                    "Assigned resource(s) overloaded",
                    IF(
                        [Resource Loading]@row < 0,
                        "Resource(s) assigned to past activity"
                    )
                )
            ),
            "Duration issue"
        ),
        IF(
            [Budget Assigned \[hrs\]]@row <> 0,
            "Resource assignment required"
        )
    )
)

Row ID

System column. Value is mirroring the sequence number of the row in the sheet (used in other formulas e.g. Successors).

Formula

=COUNTIFS(Activity$1:Activity@row, OR(@cell = "", @cell <> ""))

Caution

This is not a column formula !!!

Schedule % Complete

Determines the Schedule % Complete for an activity (or summary) at the data date (today) as a linearization of progress from the actual Start Date to the actual Finish Date, factoring in non-working days in the project calendar.

Note

The values yielded for summaries in the WBS are not accurate portrayals of Planned Value as these linearize at those summary levels only. Accurate "Planned %" is given by comp%_planned_all.

Formula

=IF(
    AND([Start Date]@row <> "", [Finish Date]@row <> ""), 
    ROUND(
        IF(
            TODAY() < [Start Date]@row, 
            0, 
            IF(
                TODAY() >= [Finish Date]@row, 
                1, 
                NETWORKDAYS([Start Date]@row, TODAY()) 
                    / NETWORKDAYS([Start Date]@row, [Finish Date]@row)
            )
        ), 
        2
    ), 
    0
)

Schedule Status

System mirror of Sheet Summary value.

Formula

=[Schedule Status]#

Scheduling Issues

Providing details on issue with activity scheduling - either activity early start (non-zero % complete and Start Date in future) or early finish (100% completed and Finish Date in future), requiring update of Start/Finish date and thus fix timing of any dependant activities. Issues in this column will be represented by a symbol in the Issue Flag column if the Sheet Summary setting SETTING - Show Scheduling Issues is checked.

Formula

=IF(
    AND(
        [WBS Descendants]@row = 0,
        [Budget \[hrs\]]@row <> 0, 
        [Start Date]@row <> "", 
        [Start Date]@row > TODAY(), 
        [% Complete]@row <> 0, [% Complete]@row <> ""
    ),
    "Activity early start - update Start Date",
    IF(
        AND(
            [WBS Descendants]@row = 0,
            [Budget \[hrs\]]@row <> 0,
            [Finish Date]@row <> "",
            [Finish Date]@row > TODAY(),
            [% Complete]@row = 1
        ),
        "Activity early finish - update Finish Date"
    )
)

Sheet

System mirror of Sheet Summary value.

Formula

=Sheet#

SPI

Schedule Performance Index (based on hours).

Formula

=IFERROR( 
    [EV \[hrs\]]@row / [PV \[hrs\]]@row, 
    ""
)

Start Date

The Start Date for the activity as most recently planned. This may differ from the Baseline equivalent (date_start_baseline) and may or may not reflect field feedback (date_start_actual) depending on whether the Project Manager intends to hold or measure the project team to earlier plans.

Status

System column. Information used for grouping purposes in various reports (e.g. Activity Progress, Activity Lookahead which is then used in the Client Dashboard...).

Note

Names of the individual statuses are chosen so that their alphabetical order corresponds to the project phases - the reason for this being the fact that Smartsheet does not allow to explicitly sort the groups in the reports.

Formula

=IF(
    AND(
        [Start Date]@row <> "", 
        [Finish Date]@row <> ""
    ), 
    IF(
        [Actual Finish Date]@row <> "", 
        IF(
            [Actual Finish Date]@row < TODAY() - 7, 
            "Finished", 
            IF(
                [Actual Finish Date]@row <= TODAY(), 
                "Finished Recently", 
                "ERROR - Actual Finish Date is in the future"
            )
        ), 
        IF(
            [Finish Date]@row < TODAY(), 
            IF(
                [% Complete]@row < 1, 
                "Finish Date Passed; Overdue", 
                "Finished - Record Actual Finish Date"
            ), 
            IF(
                [Finish Date]@row < TODAY() + 7, 
                "Finishing Soon", 
                IF(
                    [Start Date]@row <= TODAY(), 
                    IF(
                        [% Complete]@row < 1, 
                        "In Progress", 
                        "Finished - Record Actual Finish Date"
                    ), 
                    IF(
                        AND(
                            TODAY() <= [Start Date]@row, 
                            [% Complete]@row > 0, 
                            [WBS Descendants]@row = 0
                        ),
                        "Started Earlier than Planned",
                        IF(
                            [Start Date]@row < TODAY() + 7, 
                            "Starting <1 Week", 
                            IF(
                                [Start Date]@row < TODAY() + 28, 
                                "Starting Later <1M (and >1W)", 
                                IF(
                                    [Start Date]@row < TODAY() + 84, 
                                    "Starting Later <3M (and >1M)", 
                                    "Starting Later >3M"
                                )
                            )
                        )
                    )
                )
            )
        )
    ), 
    "Not scheduled"
)

Successors

Showing task numbers of all those tasks that are linked this task through any type of dependency. The field is not editable and is only used for informative purposes.

Formula

=JOIN(
    COLLECT([Row ID]:[Row ID],
            [Predecessors (Helper)]:[Predecessors (Helper)],
            FIND("|" + [Row ID]@row + "|", @cell) > 0),
    ","
)

SV

Schedule Variance (in hours).

Formula

=[EV \[hrs\]]@row - [PV \[hrs\]]@row

SV%

Schedule Variance (in hours), expressed as percentage of Planned Value (hours).

Formula

=IFERROR(
    SV@row / [PV \[hrs\]]@row, 
    ""
)

TCPI

To-Complete Performance Index, indicates how efficiently do we need to use our remaining resources.

Formula

=IFERROR(
    ([FAC \[hrs\]]@row - [EV \[hrs\]]@row) / ([FAC \[hrs\]]@row - [Actual \[hrs\]]@row),
    ""
)

Variance

Smartsheet generated column that shows the difference between the Baseline Finish Date and the Finish Date. Should yield the same result as var_date_finish_planned_to_baseline.

Formula

=IF(
    [WBS Level]@row <= 2,
    Activity@row,
    IF(
        [WBS Level]@row <= 2,
        INDEX(ANCESTORS(Activity@row), 2)
            + IF(
                [WBS Level]@row <= 3,
                "",
                " | " + INDEX(ANCESTORS(Activity@row), 3)
                    + IF(
                        [WBS Level]@row <= 4,
                        "",
                        " | " + INDEX(ANCESTORS(Activity@row), 4)
                    )
            )
    )
)    

WBS

System column.

Formula

=IF(
    [WBS Level]@row <= 2,
    Activity@row,
    IF(
        [WBS Level]@row <= 2,
        INDEX(ANCESTORS(Activity@row), 2)
            + IF(
                [WBS Level]@row <= 3,
                "",
                " | " + INDEX(ANCESTORS(Activity@row), 3)
                    + IF(
                        [WBS Level]@row <= 4,
                        "",
                        " | " + INDEX(ANCESTORS(Activity@row), 4)
                    )
            )
    )
)    

WBS Descendants

System column.

Formula

=IF( 
    [WBS Level]@row <> 1, 
    COUNT(DESCENDANTS()), 
    SUM(CHILDREN())
) 

WBS Level

System column. Represents the level in the WBS for the activity.

Formula

=COUNT(ANCESTORS()) + 1