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.

dates format when exported to excel

7 replies [Last post]
Sat, 2010-06-12 07:55
Roozbeh Taheri
User offline. Last seen 7 years 34 weeks ago. Offline
Joined: 20 May 2003
Posts: 38
Groups: None
hi

when dates are expoeted to excel(e.g. early start or early finish), in addition to date, excel shows the time as well like ’11/22/2007 11:18:00 AM.

Though Ihave selected not to show time in edit-user preferences-dates tab, but I still get the same results.

does anybody know why excel does this and how toprevent it.
Top

Replies

Wed, 2010-06-16 22:10
#1
Rodel Marasigan
User offline. Last seen 7 hours 9 min ago. Offline
Joined: 25 Oct 2006
Posts: 1699
Groups: Petro-Chem / Oil & Gas
Rouzbeh,
Once you assigned time to activity it still can adjust to 12:00Am but you schedule would have wrong output therefore yes you have to live with it.

I’m also using MS Office 2003 and replace function is working fine. You better check the default font as it has different equivalent on key stroke.
Top
Wed, 2010-06-16 21:14
#2
Roozbeh Taheri
User offline. Last seen 7 years 34 weeks ago. Offline
Joined: 20 May 2003
Posts: 38
Groups: None
Rodel,

If the activity calendar is for example 8:00 AM to 5:00 PM then time can not be set to 12:00 AM, can it.

So I guess I have to live with it. Is that correct.

Moreover, normally when exported to excel, I used VALUE formula to change the text format to date. I tried to use ctr+h and replace ’ with blank, but excel 2003 seems does not recognizes this character ’ and says couldn’t find any to replace.

Regards,
Rouzbeh
Top
Tue, 2010-06-15 08:10
#3
Rodel Marasigan
User offline. Last seen 7 hours 9 min ago. Offline
Joined: 25 Oct 2006
Posts: 1699
Groups: Petro-Chem / Oil & Gas
Roozbeh,
Edit-> Users Preferences-> Date tab-> Time-> 12 hour format and select show minute. Then you can change time for all dates.
Top
Tue, 2010-06-15 08:05
#4
Roozbeh Taheri
User offline. Last seen 7 years 34 weeks ago. Offline
Joined: 20 May 2003
Posts: 38
Groups: None
Rodel,

where in P6 we can change time to 12:00 AM

regards
Top
Sun, 2010-06-13 02:30
#5
Rodel Marasigan
User offline. Last seen 7 hours 9 min ago. Offline
Joined: 25 Oct 2006
Posts: 1699
Groups: Petro-Chem / Oil & Gas
Roozbeh,
By default P6 use 12:00 AM time and all activities having default 12:00AM will not export time format but once you change the 12:00AM default time and assigned a regular time like 08:00 AM for start and 05:00PM for end of day then it will include time format. There is no way you can prevent exporting date format only once you assigned the correct time because that’s P6 calculating base.
Top
Sat, 2010-06-12 11:35
#6
Roozbeh Taheri
User offline. Last seen 7 years 34 weeks ago. Offline
Joined: 20 May 2003
Posts: 38
Groups: None
Rodel,

thanks for your reply. in fact this is what I do every time I export to excel.

do you have any idea why some times this happens with export to excel. in some cases i noticed that the time is not exported. especially i noticed that for a project with resource dependant activities which there were no relationship between activities and activites were scheduled based on their asignments.

in addition, is there any way to prevent that. some times when I need to do frequent exports to excel, it would save time and effort for each time changing the format in excel.
Top
Sat, 2010-06-12 09:58
#7
Rodel Marasigan
User offline. Last seen 7 hours 9 min ago. Offline
Joined: 25 Oct 2006
Posts: 1699
Groups: Petro-Chem / Oil & Gas
Roozbeh,
All data exported to excel is a text format. To change the format highlight all date and press Ctrl+h (replace command). Enter ’ on find what and leave the replace with empty then click replace all. Format will change automatically to date. Highlight again and press Ctrl+1 (format cell command) and select the date format you want.
Top