Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - Tracking Labor Hours

    I need help with generating a graph to track labor hours for my operators. I have access to when the operators log off an order and the labor hours they submitted on that order.

    What I would like to do, is to be able to create a daily graph based on time, of what operator was logged into what order.

    The graph I want to make would be a stacked bar graph similiar to this:
    Operator Name: Orders that the operator was logged on, sorted by time

    Joe |Order 1................| Order 2..|..........Order 3|
    6AM 7AM 7:45AM 9AM


    Can I please get some help with this?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 328 Times in 321 Posts
    Could you attach a sample file of your data, and if nothing else, a rough picture of what you want the chart to look like...

    Steve

  4. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,086
    Thanks
    39
    Thanked 187 Times in 174 Posts
    jf,

    Here is a table that will automatically calculate the time interval and graph each employee's orders in sequence. You can change the graph to another type if you like.

    HTH,
    Maud

    Time1.png

    Time2.png
    Attached Files Attached Files

  5. #4
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's close to what I am looking for -

    I would like to arrange the data by time if possible, so I can see that which order they worked on and what time of day it was (i.e. a stacked bar graph chart that would show, say Joe from the Example above, working on order 1 from 7:45AM to 9:15AM, then order 2 from 10AM to 10:30AM and so on ).

    Also, my data only give the timestamp for when the operator stops working on an order and the overall labor hours they were logged in for. Is there an easy way to get my start times? I tried to setup an equation to subtract the labor hours from the clock out time, but it doesn't give me the operators start time.

  6. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,086
    Thanks
    39
    Thanked 187 Times in 174 Posts
    To find the start time from a given end time and time interval, paste the following formula into B3 and copy down

    =C3-TIME(0,D3*60,0)

    FindStart.png

  7. #6
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,086
    Thanks
    39
    Thanked 187 Times in 174 Posts
    If you have your interval as hh:mm instead of a decimal then place the following formula in B3 then copy down

    =C3-TIME(0,(HOUR(D3)*60)+MINUTE(D3),0)

    time3.png

  8. #7
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,086
    Thanks
    39
    Thanked 187 Times in 174 Posts
    See if this is what you are looking for. The orders in the graph are in chronological order showing the employee, Stop time, Interval, and calculated Start Time.

    Interval1.png

    Interval2.png
    Attached Files Attached Files

  9. #8
    New Lounger
    Join Date
    Apr 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I want the X-Axis on the graph to be time, the Y-Axis to be operator, and which specific order that operator was logged in on shown on the graph. I hope that makes sense. If not, I will try to make a rough drawing.

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 328 Times in 321 Posts
    To make the x-Axis time and the y-Axis operator:
    Right-click the chart
    Change Chart type
    Select the Bar chart

    As to other changes, I think a rough drawing would be useful. I also think an example file of your data setup would be good as well.


    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
  •