Formula using project plan Baseline columns that may not yet exist as Baseline hasn't been set

ronhay ✭✭✭
edited 10/12/23 in Formulas and Functions

I would like to put a formula in place for the health of milestones within project plans deployed as templates in a Control Center blueprint.

The formula would work similarly to how it works in the Project Plan template provided in the Project Management Office template set available in Smartsheet. However, the template set does not leverage the project plan baseline capability within Smartsheet project plans to store a snapshot of the current plan as a baseline (in baseline start and baseline finish columns).

Ideally, I would like to leverage the project plan baseline function for setting and maintaining schedule baselines, and use the baseline fields as part of my milestone health formula. The challenge is, baselines will not be set until sometime after provisioning of the project artifacts and an initial plan has been put together. Meanwhile, the health column should have a formula to calculate based off the delta between the current forecast (start and end fields) and the baseline (baseline start and baseline finish).

As an example, here is the initial formula:

Schedule Delta (Working Days): =IFERROR(IF(End@row = [Baseline Finish]@row, 0, IF(End@row > [Baseline Finish]@row, NETWORKDAY([Baseline Finish]@row, End@row) - 1, IF(End@row < [Baseline Finish]@row, NETWORKDAYS([Baseline Finish]@row, End@row) + 1, ""))), "")

Schedule Delta (%): =IFERROR([Schedule Delta (Working Days)]@row / Duration@row, "")

Health: =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))

On the template, the Schedule Delta (Working Days) formula results in #UNPARSEABLE because the Baseline Finish column doesn't exist yet.

Here are the options I have tried:

1) IFERROR doesn't help here because Smartsheet cannot even parse the formula due to unknown column names.

2) Set a baseline on the template so that the baseline start and finish columns are a part of the template.

2a) This requires me to enter a dummy entry with start and end dates in the project plan template so that I can create a baseline.

2b) If I use the remove baseline function, it then makes the baseline fields editable and I can blank out the baseline dates in the project plan while keeping the baseline columns. This preserves the health formula (no more #UNPARSEABLE).

2c) When I want to set a baseline again in the template, now the function sees that there are already baseline start and baseline finish columns, so it creates Baseline Start2 and Baseline Finish2 instead of updating the original baseline columns and therefore my health formula doesn't pick it up.

3) The last alternative I can think of is to not use the Baseline function built into Smartsheet plans and instead create my own baseline columns. Then I would need to have users manually copy the start and end dates into the baseline columns when they want to capture a baseline. This works, but it defeats the purpose of having this baseline (snapshot) capability in Smartsheet.

Can anyone think of another way to handle this scenario? Is there a way to get my formula to behave even though some of the columns it references do not yet exist?


  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @ronhay

    I recommend utilizing the "Reset" feature within Smartsheet Baselines. After completing steps 2) and 2a), instead of deleting the baselines as described in 2b), consider resetting the baselines after the project is created.

    Given that you initially entered a placeholder with start and end dates, the baseline values will automatically adjust when you input the actual project start and end dates. To establish these newly inputted values as the baseline, you can use the "Reset" function, which will set all variances to zero.

    In my workflow, I rely solely on variance values, and I find the "Reset" method to be quite effective for my needs.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!