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

ronhay

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

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.

Victoria K.

Hi @ronhay,

Have you had any success in finding a solution? I have a similar challenge in that my Project Managers want the Baseline included as part of the Project Plan template and dashboard metrics, but do not want any dummy dates populated upon project creation.

ronhay

@Victoria K. ,

I had to go with my option 3 to work around this limitation. I include my own baseline start and baseline finish columns in the template and instruct PMs not to use the baseline function in Smartsheet. Baseline dates have to be managed completely manually as a result but I couldn't find a workable alternative.

@jmyzk_cloudsmart_jp ,

I found that resetting the baseline when I've included my own baseline start and finish columns creates new columns (baseline start2, baseline finish2) which I don't want as other formulas will not be looking for those.

Victoria K.

@ronhay I'm in a similar boat, thanks for confirming.