none
Importing dates from Excel 2010 to columns "Start" and "Finish" doesn't work RRS feed

  • Question

  • I use Project Professional 2010 and Excel 2010 and I would like to merge data, especially dates, from an Excel Sheet to a Project file and of course I would like to see the information correctly in the Gantt chart. Therefore I use the Import Wizard. The problem is, that the dates are not recognized by Project for the columns "Start" and "Finish" - at least not as dates.

    I use the the following columns to merge the data from Excel:
    - Unique No
    - Start
    - Finish
    and further information that are related to text- and number columns in Project.

    If I import the xlsx-file by using "merge the data" and defining the "unique number" as merge key Project imports all data except the Project start and Project end dates.
    I tried several solutions like defining the data types in the Excel columns differently but Project couldn't enter the dates in the columns "Start" and "Finish", or if it entered the dates they were in data type "text" and didn't show up in the Gantt chart.

    The only thing that worked was merging data from Excel (defined as data type "date") to one of the "Date1-20" columns in project. In this case the dates are correctly shown - but then the dates are not used for the Gantt chart.
    I guess the problem is that the two columns "Start" and "Finish" are defined as data type "text" but there should be a solution, as these columns are used for the Gantt chart... I just can't find it...

    Thanks for your help!
     


    • Edited by OscarMayer Friday, April 25, 2014 9:50 AM
    Friday, April 25, 2014 9:47 AM

All replies

  • OscarMayer --

    If you try exporting a Microsoft Project 2010 project to an Excel workbook using the Project Excel Template option in the Export Wizard, you can examine the resulting Excel workbook to see how the Start and Finish dates are formatted.  I can tell you that the dates are NOT formatted as text.  They use the General formatting and they show BOTH the date and the time.  Using this information, perhaps you can adjust the data in your own Excel workbook and try importing the data again into the Microsoft Project 2010 file.  Hope this helps.


    Dale A. Howard [MVP]

    Friday, April 25, 2014 11:31 AM
    Moderator
  • Thanks for your answer. You are right: if I export my Project file to Excel the cells have a general format. But if I take my Excel sheet I want to import and type a date in a cell, this cell is automatically formatted to date. If I change it manually to general it of course changes the date to the correspondent general number. This can be imported to Project, but it then also shows as general number and not as date.
    • Edited by OscarMayer Friday, April 25, 2014 12:02 PM
    Friday, April 25, 2014 12:01 PM
  • OscarMayer,

    Just for reference I did a quick test using Project 2010 with SP2 the the June 2013 cumulative update and Excel 2010 SP2. My Excel file had 4 columns of data - UID, name, start and finish. In Excel dates were in the default Date format. I merged using the UID as the merge key. All data was correctly imported into Project.

    Note that importing both start and finish will leave a finish-no-later than constraint on the task which you may or may not want.

    John

    Friday, April 25, 2014 3:17 PM
  • I agree with John, best only to ever import actual dates. Let Project calculate the dates using task durations and links.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, April 25, 2014 9:04 PM
    Moderator