Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Import Dates from excel to P6

14 replies [Last post]
Ferdinand Ancheta
User offline. Last seen 4 years 39 weeks ago. Offline
Joined: 28 Oct 2007
Posts: 28
Groups: None

When importing an excel file with Start and Finish dates to P6, it doesn't give me the correct dates. Instead the dates shown in P6 are only one date which is the project start date. Thus, could you please advise what format of dates required in excel to import in P6? Am not sure if this is a format issue coz' i tried to use start date & durations and it works. The problem is that Start Date gives only one date which is the project start date. 

Replies

Zoltan Palffy
User offline. Last seen 2 days 21 hours ago. Offline
Joined: 13 Jul 2009
Posts: 3089
Groups: None

You should not import start and finish dates into your schedule this is what the program along with the durations, logic and calendar does for you. If you want to import start dates you have to import it as an activity with a primary constraint and the constraint date and you have to make sure the TIMES are included. If you want to import the finish date you can either import it as a secondary constraint or import an expected finish date.

You can import actual start and actual finish dates. 

Importing dates in primavera p6 from spread sheet is always tricky and it is not 100% right as per my experience, However I have a solution for you if you are going to update your program by using previously updated program or vice versa.In this case I am only changing Actuals of program w.r.t otherone .Lets say you have 2 programs

Program 1 : This is the program ON which start dates are to be imported.

Program 2 : Whose dates are to be imported on program 1.

 

Its a bit tricky . Make program 2 baseline of Program 1 and apply gobal changes

where

 BL1 activity %age is greater than 0%

change

actual date = BL1 start date

 

here In this case I am replacing only the updated dates from In-progress & completed activities of program 2 .

Hope you understand the concept .

klhyundai lal
User offline. Last seen 10 years 2 weeks ago. Offline
Joined: 4 Mar 2014
Posts: 3
Groups: None

I am using Primavera P6 v7 for my project. I have a problem. I have imported activities from excel sheet, but finish date is not changed according to duration of activity.

i.e.  Activity Start date + Duration= Finish Date

Finish date is not calculated automaticaly. how to set it to auto calculate like MS Project 2013.

 

2006
primavera_problem.png

klhyundai lal
User offline. Last seen 10 years 2 weeks ago. Offline
Joined: 4 Mar 2014
Posts: 3
Groups: None

 

Andy Bassett
User offline. Last seen 12 years 17 weeks ago. Offline
Joined: 24 Nov 2011
Posts: 4
Groups: None

Hannes

Thanks for the explanation.  Sorry, I don't doubt your expertise.  And no, my solution won't work for Early Start.  It's simply for maintaining the correct start dates when importing new activities, with start dates in the future, from a spreadsheet - which was what I was trying to do when I stumbled upon this forum.  (I suppose it would work for dates in both past and future, if the Global Change criterion was changed to user-defined field <> Start Date)

Hopefully one of our answers is the one Ferdinand requires.  Or even better, maybe he already resolved it months ago!  My solution works for my situation but may not for his, depending on his circumstances.  I was so pleased to find a solution, I just thought I'd share it.

Andy

Hannes de Bruyne
User offline. Last seen 1 year 28 weeks ago. Offline
Joined: 25 Jul 2005
Posts: 154
Groups: None

Hello Andy

Simple questions require simple answers. As Primavera works with CPM, also (Early) starts are calculated by links and durations. (Not by their own durations of course, but by the durations of their predecessors). During the planning phase, the data date is of no importance at all, so it does not have any infuence on Early Start, only during the monitoring and Control Phase this must be taken into account.

Although all your statements are correct, they might not be the right answer for this simple question. in all the years that I have been working with Primavera (and I started 20 years ago with Finest Hour) I met hundreds of planners, who did not know the very simple thing, that ES and EF are not entered but calculated by CPM rules. A lot of people still do not know the difference between Early start, Planned Start and Start dates - see discussions in the past. Therefore my simple answer could well be the answer to this question.

Hannes

Andy Bassett
User offline. Last seen 12 years 17 weeks ago. Offline
Joined: 24 Nov 2011
Posts: 4
Groups: None

Hannes

Ferdinand's problem is with Start dates, not Finish dates.  Finish dates are calculated, as you say, by duration (and affected by the calendar being used).  They always import OK, or close enough to adjust afterwards, if you include durations in the import spreadsheet. 

The problem is that imported Start dates always default to the data date.  This is fixed and unchangeable, so a work-around is needed, such as the one I posted yesterday, using user-defined date fields and a Global Change (which is not my own idea, incidentally - I got it from another forum!  I only found this thread because I had exactly the same problem as Ferdinand and was looking online for a solution).  This one truly works.  It just takes a bit of setting up first.

I hope this clears things up.

All the best

Andy

Hannes de Bruyne
User offline. Last seen 1 year 28 weeks ago. Offline
Joined: 25 Jul 2005
Posts: 154
Groups: None

Hello Ferdinand

 

this is absolutly right. Maybe you did not quit understand start and finish dates in Primavera. These dates are not entered (or imported), they are only calculated. Calculated on the basis of links and durations. E.g 3+4+5 =12 - if you export this calculation, reimport this with 13 instead of 12 you expect the result 3+4+5 = 13. This just wont work. You may import link details or new durations but never import dates. (only if these are constraint dates, which you should avoid to use as much as possible)

 

Hannes

 

Andy Bassett
User offline. Last seen 12 years 17 weeks ago. Offline
Joined: 24 Nov 2011
Posts: 4
Groups: None

Ferdinand

From what I can see here, the other people responding don't actually understand your original question.  If you haven't already found the answer elsewhere, here is a method I have come across which works - at least for activities with start dates later than the data date.

  • In P6, you create user-defined start and finish date fields and add these columns into your project layout.
  • Export to Excel (the quickest way is using Edit – Select All, then right-click and Export to Excel) so these field headers appear in the spreadsheet.
  • Paste into this spreadsheet, under these headers, the data you wish to import into P6.
  • Copy the Start Date and Finish Date and paste them into the columns for the new user-defined fields.
  • When you import it into P6, the user-defined fields will have the correct dates.
  • In P6, under Tools, select Global Change and create a new one as follows:
    1.   If  <user start date> is greater than DD  (data date)
    2.   Then <Planned Start> = <user start date>
  • Run the change and the start date is corrected.  Finish date automatically corrects according to the activity duration.

The user-defined finish date might appear redundant for this process but it is useful for comparing dates after the Global Change has been run, as different calendars will affect the finish date.

As adding the user fields and creating the Global Change are one-off activities, it’s now set up for future imports.

Cheers

Andy

Joseph Erwin Carg...
User offline. Last seen 6 years 50 weeks ago. Offline
Joined: 7 May 2006
Posts: 33
Good day, Dear all, Please give me the link of discussion for the procedure om export and importing in P6, or you can give me in this thread. thank you. Joseph
Andy Power
User offline. Last seen 3 years 27 weeks ago. Offline
Joined: 30 Aug 2006
Posts: 43
Groups: None

Ferdinand

The finish dates are calculated from the start date and the duration, make sure in the user preferences that you have the correct duration format selected (ie, if your xls durations are in days select days in P6).  It will also be worth checking your project calendar as this needs to match, holidays, weekends etc ...  Then import and schedule (F9) and you should have the correct dates.

Andy

Irfan Khan
User offline. Last seen 7 weeks 2 days ago. Offline
Joined: 19 Jul 2009
Posts: 138

hi Ferdinand,

               Hope u followed my last instructions. As u said u r unable to see accurate dates in P6 after export from Excel.

After export from excel go in P6 > Clink on Edit > User Preferences > go on dates tab > there is date fomates chk any one what u like (u can see sample below) .

 

regards,,,,

Ferdinand Ancheta
User offline. Last seen 4 years 39 weeks ago. Offline
Joined: 28 Oct 2007
Posts: 28
Groups: None

At the outset Irfan, thanks for your reply. However, could you please clarify coz' i didn't understand well? To reiterate, i'm having problem importing the dates from excel and it didn't give me the correct dates in P6. Hence, my query is that if my dates in excel would be the culprit, if so, what would be the correct format of dates to export in P6?

Irfan Khan
User offline. Last seen 7 weeks 2 days ago. Offline
Joined: 19 Jul 2009
Posts: 138

Hi Ferdinand,

 

After export ur data into excel > come in task Tab (in excel sheet) > come on column 'start_date '(*)Start > Inset a colum behind this column > copy all ur dated > use paste special in next column > & select add option from paste special.

i thinks it ill help u......

 

regards,,,,,,

 

Irfan Khan