none
Updating Actual Start/Finish Dates with Excel Merge Breaks all links RRS feed

  • Question

  • I have a WBS that requires weekly status updates on Actual Start Date, Actual Finish Date, and % Complete from maintenance supervisors who do not have access to MS Project. Currently, my planner is entering the data manually (in Project 2013). We attempted to update the data by merging the Excel data file using the UID as the merger key. Whenever the merge is completed, all resources and predecessor/successor allocations/links are gone. I can still see the resource allocations in the Resource Sheet, but cannot link them to the tasks.

    All of the documentation I can find anywhere makes the merge update a very simple and straightforward task (I am using an exported MS Project Excel file to test the import and still have the same issue). Here are the steps I am taking:

    • "Open" the .xlsx file (which contains only the four columns: UID, Actual_Start, Actual_Finish, and %Complete)
    • Choose "New Map"
    • "Merge the data into the active project"
    • Select "Tasks" only and "Import includes headers"
    • Using the Unique_ID as the Merge Key and mapping the fields to their original MS Project Fields
    I haven't seen this same issue discussed anywhere else, so my initial thought is that there is something odd about how the project was initially designed. Any help is much appreciated.
    Thursday, October 1, 2015 4:39 PM

All replies

  • Richard,

    First of all, is your version of Project 2013 fully updated? That includes the September 2015 public update which will put your version number at:

    15.0.4753.1000

    If it is not, you can do a Windows update and that will pick up the latest updates for Office and Project.

    One issue I see in your import is that actual finish and percent complete must track exactly. In fact, if a task has an actual finish date, I would not import percent complete for that task because Project automatically sets the percent complete of any task with an actual finish at 100%.

    It's possible your file is corrupt. Try saving as XML and then re-open in Project. Note, you will lose any custom formatting using this process.

    John

    Thursday, October 1, 2015 5:04 PM
  • John,

    Thank you for the response. I tried the XML approach, and had no success. As for the version, Project itself shows 15.0.4753.1000 (which I see has an update to fix issues with Excel merges), but the control panel "Programs and Features" shows 15.0.4569.1506, which is odd. I am re-running my updates, but it looks like it is updated.

    Friday, October 2, 2015 3:48 PM
  • Richard,

    If Project is showing a version of 15.0.4753.1000 then it is fully updated. Don't worry about what "Programs and Features" shows.

    Just for reference last night I created a simple Project file, exported it to Excel and then tried an import with merge. I got nothing (i.e. no fields were updated), but then I know the export/import wizard is more "fussy" then it used to be with earlier versions. I have been able to import as new, append, and merge previously so I know it works, but I'll need to do more testing.

    Meanwhile, does this issue only occur if you try to import an exported Project file or will things also get messed up if you simply create an Excel file with the desired fields and then import using merge?

    John

    Friday, October 2, 2015 5:00 PM
  • John,

    It doesn't matter which way I do it. I have even tried variations in Excel where I only import one of the fields, or even a limited subset of rows with only one field, and it doesn't seem to make any difference. I would attempt to "sanitize" the file, but it doesn't appear that I can attach anything in this forum (for understandable reasons). Even our local expert (who built the file) can't seem to figure out the issue. We have one planner working with Project 2010, and it has the same problems.

    Friday, October 2, 2015 6:06 PM
  • Richard,

    As I mentioned earlier, Project is rather fussy when importing data from Excel. Since the data is all imported as text, it is important to have the data in text format in Excel. Further Excel does not recognize "working time" like Project so all dates in Excel start at 12:00 AM unless specifically entered otherwise. If importing dates into the Start and Finish fields of Project, Project will automatically adjust the start time to be consistent with the Project calendar. However, all other date fields will be imported exactly is they appear in Excel (i.e. 10/5/15 will import as 10/5/15 12:00 AM). If this date is imported directly into the Actual Start or Actual Finish field in Project, the result will not be what the user expects. And of course, "NA" is a special case for Project date fields. If you attempt to import an "NA" date into Project's Actual Start field, the import wizard will ignore that "NA" and instead "remember" the last date that was imported into an Actual Start field.

    Obviously there are many pitfalls when importing data into Project. The approach that seems to work the best is to import data such as Percent Complete, Actual Start, Actual Finish, etc. into extra text fields (e.g. Percent Complete into Text1, Actual Start into Text2, etc.). Once the data is in Project, then the user can "clean up" the values and copy to the desired fields.

    In my opinion a better approach is to use a VBA to import Excel data into Project. The macro code can automatically take care of the items noted above and eliminate any clean up.

    For reference, I'm in the process of writing an Wiki article on this subject. When it's ready I'll post back with a link.

    John

    Monday, October 5, 2015 2:32 AM
  • Thank you for all the assistance, John. It certainly appears that VBA is the way to go, so I'll head down that path.
    Monday, October 5, 2015 5:35 PM
  • Richard,

    You're welcome and thanks for the feedback. If this answered your question, please mark it as answered or if it was helpful, please give a vote.

    John

    Monday, October 5, 2015 7:09 PM