Exporting Work Hours (2000)

Topic

New Reply

Hi,
I have an Access database that I want to import the Remaining Work Hours into. The problem is, when I import them it keeps “hrs” on the end of the number.
Example: “1,056 hrs” or “96 hrs”. Is there a way to convert the Remaining Work Hours data into a number so I can calculate with it in Excel or Access?

Thanks in advance.
Deb Buck

Reply | Quote

Replies

I normally take the data out to excel. insert a column alongside the column referring to hours. Select the data and then use Data Menu | Text to Coumns, In the second step fo the wizards I select spaces as the delimiter. If when I have exported data I get a mixture of hrs, days, etc I write a series of if statements in another colurms to convert everything to hourrs. eg:=If(B2=”days”, A2*7,If(B2=”wks”,A2*35,A2)I can then delete out the uneccessary A2 and B2 (assuming my formula was in C2)

Reply | Quote

I’ll give it a shot thanks.
Deb

Reply | Quote

I’ll give it a shot thanks.
Deb

Reply | Quote

I normally take the data out to excel. insert a column alongside the column referring to hours. Select the data and then use Data Menu | Text to Coumns, In the second step fo the wizards I select spaces as the delimiter. If when I have exported data I get a mixture of hrs, days, etc I write a series of if statements in another colurms to convert everything to hourrs. eg:=If(B2=”days”, A2*7,If(B2=”wks”,A2*35,A2)I can then delete out the uneccessary A2 and B2 (assuming my formula was in C2)

Reply | Quote

Deb,

How are you importing the hours from Proejct to Access? Are you using access to connect directly to MS Project via ODBC, using Access VB to start project and get the data, or are you exporting data to a .csv file and importing the data into Access?

Reply | Quote

Gary,
I tried it 2 ways, Project to an Excel file, then to Access, but the “hrs” goes with the data so I had to get rid of that which I did with the Left and Len functions. Then I tried exporting from Project with a Map directly to Access into a table, but that didn’t really work either. I don’t know VB and I didn’t think of a .csv file. What would be best. I need to do calculations so I need the Hrs fields to be numbers.
Thanks,
Deb

Reply | Quote

To get project data to go directly to access you would need to first save the MS Project file as a Microsoft Access Database. Open the Microsoft Access Database in access to see the MS Project table structure and data.

The tables contain the data you need as long as you understand where the data is stored and how to convert it to something understandable. For example, to see the duration of each task, open table MSP_TASKS and review the TASK_DUR field. You would need to divide this value by 4800 to convert it to days.

So you would need to track down an ERD / Data Dictionary to understand which data fields you would need and then convert it to hours.

Of course, this is easier said then done.

On an easier side, if you export all of your data in hours, then in excel use the search and replace feature to just remove the “h” exported with the data. You could also write a macro that looks at the last character of the field and then converts the data based on the result.

HTH

Reply | Quote

Gary,
Thanks, that’s pretty much what i found. I was fighting with what to divide by and how to get rid of the “hrs” in Access. Right now I’m using Excel and I’m getting the job done.
Thanks for your help.
Deb

Reply | Quote

Gary,
Thanks, that’s pretty much what i found. I was fighting with what to divide by and how to get rid of the “hrs” in Access. Right now I’m using Excel and I’m getting the job done.
Thanks for your help.
Deb

Reply | Quote

To get project data to go directly to access you would need to first save the MS Project file as a Microsoft Access Database. Open the Microsoft Access Database in access to see the MS Project table structure and data.

The tables contain the data you need as long as you understand where the data is stored and how to convert it to something understandable. For example, to see the duration of each task, open table MSP_TASKS and review the TASK_DUR field. You would need to divide this value by 4800 to convert it to days.

So you would need to track down an ERD / Data Dictionary to understand which data fields you would need and then convert it to hours.

Of course, this is easier said then done.

On an easier side, if you export all of your data in hours, then in excel use the search and replace feature to just remove the “h” exported with the data. You could also write a macro that looks at the last character of the field and then converts the data based on the result.

HTH

Reply | Quote

Gary,
I tried it 2 ways, Project to an Excel file, then to Access, but the “hrs” goes with the data so I had to get rid of that which I did with the Left and Len functions. Then I tried exporting from Project with a Map directly to Access into a table, but that didn’t really work either. I don’t know VB and I didn’t think of a .csv file. What would be best. I need to do calculations so I need the Hrs fields to be numbers.
Thanks,
Deb

Reply | Quote

Deb,

How are you importing the hours from Proejct to Access? Are you using access to connect directly to MS Project via ODBC, using Access VB to start project and get the data, or are you exporting data to a .csv file and importing the data into Access?

Reply | Quote

The easiest way to remove the ‘hrs’ from your hours data is to 1) export your Project Data into Excel 2) highlight the column where you want the ‘hrs’ removed. 3) Choose Data from the Menu bar 4) Choose Text to Columns option and follow the prompts (be sure to use ‘spaces’ as a separator. It works like a charm and it beats writing special macros or scripts. I routinely pull data out of Project and move to Pivot tables for more flexible resource management reporting. I have to use this conversion each time I pull my data from my Master Project Plans. Good Luck.
TommyB

Reply | Quote

The easiest way to remove the ‘hrs’ from your hours data is to 1) export your Project Data into Excel 2) highlight the column where you want the ‘hrs’ removed. 3) Choose Data from the Menu bar 4) Choose Text to Columns option and follow the prompts (be sure to use ‘spaces’ as a separator. It works like a charm and it beats writing special macros or scripts. I routinely pull data out of Project and move to Pivot tables for more flexible resource management reporting. I have to use this conversion each time I pull my data from my Master Project Plans. Good Luck.
TommyB

Reply | Quote