none
Import from Excel to Project - blanks cells are having dates automatically added RRS feed

  • Question

  • Hi. I'm very new to Project but have started setting up a Roadmap using it which is working fairly well so far, although have hit one snag.

    I have data in Excel: it is a list of projects with 4 columns containing the dates of various stages (planning, initiate, execute, close). I have created a reusable map so this can be imported into Project and the Gantt chart to show pre-defined markers on the timeline for each stage on each project row. All well and good so far.

    However, not all of the projects in Excel have all of their dates defined. E.g. a project might not yet have a close date. But when this is imported into Project a date gets applied for it. I have no idea why!

    How do i get the import into Project to allow any blank cells from Excel to remain as blank so that nothing is plotted on the Gantt chart for that particular section?

    Wednesday, February 26, 2014 11:59 AM

All replies

  • Andy --

    Here is an idea for you:  in your Excel spreadsheet, add one additional column called Task Mode.  For every task that is missing a date, set the Task Mode value to Manually Scheduled.  For every task that has all of the necessary dates, set the Task Mode value to Auto Scheduled.  This is assuming that you are using either the 2010 or 2013 versions of Microsoft Project, however.  Hope this helps.


    Dale A. Howard [MVP]

    Wednesday, February 26, 2014 2:09 PM
    Moderator
  • As Dale said (I just tested it), import you Excel spreadsheet with the task mode column (automatically scheduled or manually scheduled) will create tasks in MS Project (2010 or 2013) with blank cells for start and finish dates (assuming corresponding cells were blank in the Excel).

    Then after the import

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Wednesday, February 26, 2014 2:18 PM
    Moderator
  • Thanks for both of your replies. I have tried this and the same issue occurs. I have checked each row in the imported Project data and they are all showing as set to 'manually selected' yet at some point in the import Project is adding data to blank cells.

    I have retried using a simple test excel sheet and when this too is mapped into Project, any blank cells get populated with the date that appeared in the preceding row for some reason:

    I can then go through and manually delete this cell in Project (which then replaces it with an "NA") - but if i have to manually do this whenever importing from Excel into Project then it makes it very time consuming.

    Surely there must be a way for Project to not auto-populate blank cells? Or am i missing something fundamental?

    Friday, March 7, 2014 4:28 PM
  • Hi Andy,

    In worst case, if you can resolve your issue and still have dates for manually scheduled tasks where you don't want to and since it is just for one unique project, I would simply adviced to select all dates and delete them. Since it is for manually scheduled tasks, it'll work.

    It will surely be less time consuming than trying to debug your issue.

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Friday, March 7, 2014 4:32 PM
    Moderator
  • Was just going through my bookmarks and realised i hadn't given my solution for this. Thanks for all of the advice above. It helped me when i was stuck and got me more used to MS Project.

    I have since had time to play with MS Project more and have implemented my solution which is working quite well. The import/map features of Project are not great, with seemingly no way to stop it auto populating blank date fields. My solution is VBA, where it remotely opens a pre-determined Excel file and remotely calls a macro stored on with the Excel file. This refreshes the data and copies it to the clipboard. The initial macro then pastes it into Project and closes Excel once completed. Took me a while to develop it (i'm a VBA newbie) but it is working quite smoothly.

    Tuesday, April 15, 2014 9:58 AM