Developing a Progress Updating Input Sheet RRS feed

  • Question

  • I've tried a few ways to create an Excel sheet from Project that will allow me to request the various task owners (e.g., the Procurement Group) to input the status (actual start, % complete, actual finish, forecast finish, etc.) of their immediate (that is, current or near-term) tasks and send it back to me for input into the schedule.  Does anyone have what they believe to be a good way to create this sort of progress updating spreadsheet?



    Friday, June 22, 2018 5:59 PM

All replies

  • Bill,

    I've stated before in my responses to other similar posts that since percent complete is strictly duration based, it is not a good measure of progress on a task. The passage of time (i.e. duration) accomplishes nothing. It is expended effort (i.e. work) that accomplishes something. Therefore I would say that a better set of metrics to populate in your spreadsheet is:

    Actual Start, Actual Work, Remaining Work

    Actual start puts a "stake in the ground" as to when the task really started. It may be the planned start date (i.e. Scheduled Start) or it may be an earlier or later date. Actual work is of course the amount of effort expended, normally as of the status date. And remaining work captures how much more effort will be required to complete the task. Given those parameters, Project will automatically calculate the forecast finish date.

    Once you have the spreadsheet updates from the task owners, you should be able to import the data into project using a merge key. A good merge key is the Unique ID field since it is permanently defined for a given task.

    Hope this helps.


    Friday, June 22, 2018 8:58 PM
  • Bill,

    John and I take slightly different approaches which both converge to the right answer.
    And we definitely agree that % complete is not useful.

    People take a % of one thing and assume that it is also a % of something else.
    I would forbid any discussion of progress which involves percentages..

    I would say that the expenditure of duration, work or cost are not necessarily progree.

    In the background there is the task itself, which might be "lay 10000 bricks" and the estimated duration was 10 days.

    Perhaps at the end of day 6, the task started on schedule 6 days ago, and has 6 days of actual duration.
    The task is 60% complete because in MSP the definition of % complete is 6 days actual divided by 10 days total.
    But at this point, there is no information about the work, the cost or the bricks, each of which will have an actual, remaining and total, and the % of each of them can be anything from 0 to 100. The task may not have any resources assigned and there may not be any work or any cost in the plan. But they are not strictly necessary because there is definitely duration, actual and remaining. If 6000 bricks have been laid, then the remaining 4 days of duration is Ok. If only 3000 bricks have been laid then the remaining duration should be increased and if more than 6000 bricks have been laid then the remaining duration should be decreased. By how much is just a matter of estimation, just as it was before any progress occurred. The "how much" decision may be influenced by the actual work and actual cost if they have been assigned, but if not then just go with an estimate. One thing is for certain, and that is that any actual work and actual cost must have occurred on the days of actual duration, so I always nail down the actual start and the actual duration before anything else.

    Your spreadsheet should capture only what's needed, but not less. Leave out any percentages, and "forecast finish" is better expressed as remaining duration.

    Saturday, June 23, 2018 3:20 AM
  • Thank you John and Trevor.  I know your replies were well intentioned and I appreciate the advice, which makes perfect sense.  However, this is a situation where I'm trying to make the software tool support my current organization's approach to doing things, rather than the other way around. 

    I'm filling a role vacated by a previous scheduler who used P6.  He would create a one-week back, two-week look ahead filter of some sort, print it and send it around for hand mark up.  We tend to think in terms of what percent complete we currently are with a task and when we expect to finish it.  We don't currently enter "work" information into our schedules, so that concept would be foreign to everyone, will take time to develop and would probably not produce any more accurate reporting than our current way of approaching progress updating, which is pretty much faith-based in any event.

    So what I'm really looking for are suggestions of ways to use Project to create a progress reporting spreadsheet that I can send around for electronic (rather than hand written) updating, potentially with the ability to import the updates back into Project in a single step.

    If anyone has any suggestions, I'm all ears.  Meanwhile, I'm playing with it on my own to see what I can come up with.

    Thanks again for your feedback,


    Wednesday, June 27, 2018 5:13 PM
  • Bill,

    With regard to your statement, "trying to make the software tool support my current organization's approach to dong things, rather than the other way around", here is my comment and then we'll go on. Project, and other project management applications, were developed to dovetail with tried and true processes for effectively managing projects. If Project is not quite meeting your organization's approach, you might want to seriously consider a paradigm shift in your organization's approach to managing projects.

    That said, let's take a look at your "faith-based" (I'll call it SWAG) updating process and see how we may be able to help you. Given a fixed set of Project fields you want to update, it is pretty simple and straightforward to create the "one-week back" and "two-week look ahead" filters and ship that information off to an Excel spreadsheet for update by those who have the "faith", so to speak. And again, with very basic fields, it's not to difficult to import the updated Excel data back into Project. One method of course is with export and import maps, although the import can sometimes be challenging given that Project has a very defined data structure, whereas Excel is basically "free form". My preference is to use VBA to both get data from Project to Excel and then back after update.

    Hope this helps.


    Wednesday, June 27, 2018 6:37 PM
  • For large projects with a lot of component managers, I've had some success exporting Project data to Excel, then distributing, retrieving, and merging stakeholder updates in Excel before re-importing to Project.  The key data for updating are Actual Start, Actual Finish, Expected Start (if should have started but didn't), Expected Finish (if an Actual Start exists), and some notes/excuses fields.  The update worksheets need to include a lot of reference data (e.g. Project Notes, scheduled start/finish dates, predecessors/successors, what the updater said on previous updates...) and need to be filtered to show only tasks that should have been progressed for specific updaters.  A fair amount of conditional formatting and data-validation logic is necessary to ensure good data.  Updaters also need to be coached on the distinction between "done enough" (for successors to start) and "done-done."

    Merging the data into a single file for re-importing to Project requires some Excel vba/macro skills.  In the past I routinely used Project's Import Wizard to import the merged updates into custom date and text fields, then I would manually post-process in Project.  Nowadays it would be simpler to use some Project vba/macros to accomplish the same end.

    This is all for time-management only.  

    Good luck, tom


    Thursday, June 28, 2018 3:07 PM
  • Bill,

    since your program/schedule does not include resource assignments, work or cost, your problem simplifies into consideration of what you've got, which is the dates and durations of the tasks, and the predecessor/successor relationships between them.

    Almost anything is better than printing off a Gantt chart and asking for penciled handwritten markups, which will probably be illegible, indecipherable, incomplete, insufficient and just plain otherwise un-usable.

    A simple spreadsheet with a list of tasks and some columns for capturing the essential information about the progress status of each one is better. The question is only "which columns (and how should they be arranged)?"

    The trick is to ask and answer the right questions in the right order, and use the information the right way.

    The first question is "has the task actually started?". The answer is either yes or no. If it has actually started, then the next question is "when did it actually start?". Since this date and time is a fact there cannot be any differences of opinion about it.

    The first column on your spreadsheet should be for a yes/no answer for "has the task actually started?".

    If you look at the tracking table, you see that the first column is the actual start date. That should be the next column on your spreadsheet. A task has either actually started or it hasn't. If it was scheduled to start in the past (relative to the current status date) but it has not actually started, then it must be re-scheduled to start asap in the future. There is a "move" button on the task ribbon and you just move the incomplete parts (all of it) to the status date.

    The next column on your spreadsheet should be for a yes/no answer for "has the task actually finished?".

    If the answer is yes, a task has also actually finished, then you can also fill in the actual finish field in the tracking table, and obviously it is another date/time in the past.

    That just leaves tasks which have actually started but not yet actually finished. In this case, you need the actual duration from when it actually started up to the status date. If you nail down the actual start date/time, and then click on mark on track, MSP will fill in the actual duration for you.

    The only other column you need on the spreadsheet is an estimate of the remaining duration. This will either be the entire duration for a task with no actual start date, or the remaining duration of a task in progress.

    One you have extracted the essential information from the people who have it, getting it out of the spreadsheet and into MSP is as simple as copy/paste or import macros or even VBA. The hard part is getting the facts in the first place.

    Thursday, June 28, 2018 10:16 PM