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.

Importing excel dates- changing the 00:00 to 17:00

4 replies [Last post]
John Reeves
User offline. Last seen 8 weeks 2 days ago. Offline
Joined: 10 May 2013
Posts: 342
Groups: None

Importing excel dates- changing the 00:00 to 17:00 .  The odd thing is when you look at the excel "bar" it shows 5/16/2018 12:00:00.  I have had schedule effected by the "hours" so i want it 5pm or 17:00.  I tried replace but there was no excel option with the date and time and am/pm.  I am not a big fan of the way P6 "always secretly includes hours", seems like P3 was easier.  I was watching a planacademy video about it and sometimes he changed the time and sometimes he did not - strange.  I have given up changing it automatically with replace all in excel, maybe I will have to change all in P6 - we will see.

Replies

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

glad I could help

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

glad I could help

John Reeves
User offline. Last seen 8 weeks 2 days ago. Offline
Joined: 10 May 2013
Posts: 342
Groups: None

Thanks! that was extremely helpful, you are great.

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

couple of things

1st the data date should be 8:00 am or the earliest times of any of your calendars.

2nd you can do a simple find and replace in excel find 12:00:00 AM and replace with 5:00:00 PM

the option is use the concatintion in excel lets say the cell in excel you need to change is cell F2 and lets say that cell H2 had the date ONLY now in cell I2 time in ' 5:00:00 PM now in cell J2 put in H2&I2

now copy this formula downs and then copy column J and pastes special VALUES for column J  

3rd if you are trying to CHANGE TIMES when importing then for completed activities ONLY import the finish date and time first because if you have a 1 day activity and the start and finish dates are the same you can NOT make the start date TIME later than the finish date TIME so you have to change the finish date TIME FIRST then change the start date TIME.

this will be a 2 step process.