Importing MS Project changes % Complete

jkbaker

I exported a MS Project to XML then imported the XML into SmartSheet. When I compared the % Complete from MS Project to SmartSheet the totals were off in many cases for the parent rows. Each child row the % Complete matches.

How can I import a MS Project and ensure the data matches for children rows as well as the parent rows?

Genevieve P.

Hi @jkbaker

If you're using Smartsheet's Project Settings, then the Parent rows have a functionality called the Parent Rollup. This provides a weighted average of the Child Rows based on Duration as well as percent complete, does that make sense?

For example, if one Child Row is at 100% but it only takes 1 day, and the Second Child Row is at 1% and it takes 10 days, then the Parent will say 10% instead of 50%.

See: Parent Rollup Functionality

Cheers,

Genevieve

jkbaker

Hello,

A couple more examples..

I re-imported a new version into a temp space. You will see the dates are very close to matching for tasks 52 – 60. You will also see the % Complete is the same for each of the child tasks 52-60. What is different is task 50 in SmartSheet is 53% Complete. The same task in MS Project is 60% Complete. If the child tasks match by date and % Complete why is the Parent (Task 50) off by 7%?

The other issue that I am having is the Non-Working days is not working. I have added 12/27/21 - 12/31/21 and those dates are showing as working days in the SmartSheet project.

jkbaker

Below is what I have setup for Non-Work Days

Genevieve P.

Hi @jkbaker

Smartsheet and MS Project are separate programs so it's expected that there will be some differences in how they calculate certain elements. I can't speak to MS Project, but each Parent row in Smartsheet looks all the of the children (including the individual grandchildren rows under the Workshop row) below it to calculate weighted percent.

You can re-create a similar formula by using the AVGW function. If you create a helper column to pull the Duration as a value ( =Duration@row) then you can use this:

=AVGW(CHILDREN([% Complete]@row), CHILDREN([Helper Duration Value]@row))

I see in these images that your dates/rows aren't exactly the same, as well. For example, the Training parent row has a duration of 29d in MS Project, but only 17d in Smartsheet. This row is under the overall Focus line, so that will affect the Focus percent.

In regards to the working days, would you be able to clarify what you mean when you say that the dates show as working days? After adding in your holiday dates, my durations adjusted as expected to show less time when the tasks spanned over holidays (ex. Data Migration changed from being 116d without the holidays to 112d, as it is in your Smartsheet project, too).

Here's more information on Smartsheet's Project settings:

SmartStart: Project Management

• Enable Dependencies and Use Predecessors
• Project Sheet Columns: Start Date, End Date, Duration, % Complete and Predecessors
• Parent Rollup Functionality
• Modify Project Settings
• Define Working Days, Non-working Days, and Holidays on a Project Sheet

I hope that helps!

Genevieve