% Complete (plan) formula based on baseline dates

jenniferrdz

Hello,

I have my actual % complete based on start and end dates. Now I'm trying to create a % Complete (plan) column based on status date and baselines dates like in MS Project.

In MS Project you dot he following

If([Baseline Estimated Finish]<=[Status Date],1,IIf([Baseline Estimated Start]>[Status Date],0,ProjDateDiff([Baseline Estimated Start],[Status Date])/ProjDateDiff([Baseline Estimated Start],[Baseline Estimated Finish])))

How can I achieve this in smartsheet?

Thank you.

SoS | Dan Palenchar

Hello @jenniferrdz !

Your exact formula will depend on a few factors such as if you want weekends and holidays to count as working days (i.e., factored into the number of days to complete a task or not) and if you want the current day to be the point a task is planned to be completed versus the last day to work (i.e., a task due on the current day is or is not shown as 100% complete).

Note that Smartsheet project settings don't count non working days in the build in duration, they essentially use the NETWORKDAY() function.

I prepared the following example with a formula in % Complete (Planned) that computes:

The number of days past the Baseline Start) / (The duration between Baseline Start and Baseline Finish in non-working days).

Note that the formula returns 0% for tasks that start in the future and 100% for tasks that should be complete.

There are a few other columns for context as well.

The formula is:

=IF(TODAY() < [Baseline Start]@row, 0, IF(TODAY() > [Baseline Finish]@row, 1, NETWORKDAY([Baseline Start]@row, TODAY()) / NETWORKDAY([Baseline Start]@row, [Baseline Finish]@row)))

Hope that helps!