Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Bluefield, West Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    resource tracking chart (Office 2000, MS Exchange Server)

    If I have an Access database that lists the times several devices were logged onto and also logged off during a 24 hour period, is there a way to import this information into Excel and make a Gantt-like chart that shows in a floating-bar format on a timeline of 24 hours when each device was on? I need to have all the times for a single device on a single line of the chart, and the lines for all the devices on the same chart for comparison. I can create a chart that puts each separate logon on a line, but I can't get all the logons for a device on one line.
    Thanks for your help.

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    Rose

    Any chance you can post a part of your existing solution? Helps the imagination work.

    Regards
    Peter

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    I agree with Peter that it would help to have some representative data.

    It should be possible (if I understand correctly), by using a stacked bar.

    You will have to calculate the time differences not just the start and end, since stacked bars ADD.

    You will have a column (or row if you transpose it) for Each "downtime" and for each "Uptime"
    Different machines can share columns (or row), each machine on a different row (or column)

    I would guess all the Uptime data would have the same format
    All the downtime data I would use no border/ no area

    If you want each machine to have different colors, it can get very cumbersome, but that is also doable. It depends on how many up/down time shifts you have. the more their are the more cumbersome this technique can get.

    Steve

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    Bluefield, West Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    I have attached a sample worksheet imported from an Access query. This is the data from one unit for one day and includes several computers. I only calculated the number of minutes for each logon. When you say I have to calculate the time differences, exactly which differences do you mean? This is where I just can't envision what I need to calculate. I am beginning to think that I am too dense for this.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Bluefield, West Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    I am now attaching the spreadsheet with the chart that I have been able to create. I limited it to two devices to make it readable. When I try to include all computers, there just isn't enough room, since each use of each computer takes a separate line. What I want to do is put all the uses of one computer on the same line, but that is where I fail.
    For this chart, I only had to calculate the elapsed time that the computer was being used. I used a stacked bar chart and formatted it so that the downtimes were "invisible". My two series were "From" and "Minutes", with "Device" as the Category X axis labels.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    Try this.

    I did most things with formulas, though you might want to use a macro if it gets too calculation intensive.

    I did create a macro to change the chart colors since you have so many devices and ON-OFF sets that there were just too many ranges to do it by hand.

    Some of you devices come ON before they go OFF(?) giving "#################" data in the OFF column, but that is your data problem not the spreadsheet's.

    i added some notes, look at it and see what you think. It could be macro driven relatively simply, though I leave that to you and how you get your data.

    Steve
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    Bluefield, West Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    Steve,
    Thank you so much! I know this took more time than I deserve. It works, and I am now trying it with a new set of data. I don't know the protocol here. Am I supposed to send you gifts, flowers, cars...? Or will you be satisfied with my undying gratitude?
    Thanks again.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    Usually promising the soul of your first born has been sufficient in the past, but maybe I should raise my rates....

    Steve

  9. #9
    New Lounger
    Join Date
    Sep 2002
    Location
    Bluefield, West Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    I noticed you were rather brief in your instruction #9 "Create chart based on your data." <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> Is there a short way to create each of the 3 billion series, or does one have to create each one individually? I do click on the worksheet, rather than typing everythng in. Whatever - it keeps me busy and makes me look like I'm working on something. And I really do thank you for your help.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    Standard way to create a chart from formatted range (which is why I set it up this way):

    Highlight the area to chart (in my example P3:AE113)
    Click on Insert Chart
    Select Bar (stacked Bar) <next>
    Series in "ROW" <next>
    Change any options you want (I would get rid of legends!) <next>
    Select where you want chart <finish>

    Run the macro I created to "hide" blanks and change non-blanks to red (can be changed in the macro)

    To add more to an existing chart:
    Right click on sheet - select source data
    Expand/contract the range displayed <ok>
    Run macro if necessary to color new items added

    Steve

  11. #11
    New Lounger
    Join Date
    Sep 2002
    Location
    Bluefield, West Virginia, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    I neglected to change to series in ROWS, thus resulting in thinking I must have to create each of the series in the chart. I am definitely too inattentive to detail for this task. Thank God for you.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: resource tracking chart (Office 2000, MS Exchange Server)

    I usually would have set up the chart the other way (Devices down the rows, occurences across the column) so there would have been no need to change to rows, but it just seemed easier to setup the data this way with so many poosible occurences.

    Steve

Posting Permissions

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