Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Work Hours (2000)

    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

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Work Hours (2000)

    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)

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Work Hours (2000)

    I'll give it a shot thanks.
    Deb

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Exporting Work Hours (2000)

    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?
    Regards,

    Gary
    (It's been a while!)

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Work Hours (2000)

    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

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Exporting Work Hours (2000)

    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
    Regards,

    Gary
    (It's been a while!)

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Work Hours (2000)

    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

  8. #8
    New Lounger
    Join Date
    Jun 2003
    Location
    Detroit, Michigan, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Work Hours (2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •