Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    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. #2
    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
    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

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 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

  4. #4
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    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.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 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

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 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

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 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

  8. #8
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    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.

  9. #9
    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
    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

  10. #10
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    To make the x-Axis time and the y-Axis operator:
    Labor Hour Tracking.jpgRight-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

    Here is an example of what I would like it to look like:
    Labor Hour Tracking.jpg

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    How about something like this:

    Chart1.png

    Chart3.png

    HTH,
    Maud
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Apr 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    How about something like this:

    Chart1.png

    Chart3.png

    HTH,
    Maud

    Yes - that should work! I'll have the data and see if I can replicate that with my data.

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Maud

    I liked your file.
    But I couldn't help myself (as RG would say) to modify it a bit.
    So how about this one:
    rz-chart.PNG

    I was trying to match the nice hand drawn picture.

    In my attached file, the bar chart descriptions can be modified.
    Up to six jobs can be entered for each person.
    You only enter the Start and Stop times.
    You enter a description for the Order.

    If you want to see the hours, there are buttons to toggle the display of these columns ON/OFF

    A change event in the 'Order Description' columns places the actual text description entered as a 'custom number format' in an adjacent column. The adjacent columns are the ones that are 'plotted'. The column is NOT hidden (this would not display in the chart if it was), rather, the adjacent columns have a very 'narrow' column width (to 'effectively' hide them).
    As descriptions are entered/edited etc etc, there will be a 'build-up' of unwanted custom formats, so I added a button to link to some code from HansV to remove unused custom formats. It blinks a lot, perhaps you could improve it???

    zeddy
    Attached Files Attached Files

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Zeddy,

    Nice mods. I had a heck of a time trying to format the X axis to present with on the hour ticks. Nice touch with the collapsing columns.

    Maud

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..I couldn't have done it without your start.
    To get the text labels into the bars, I learnt that you could use the actual data Value for your label!!
    But instead of showing the duration hours number, if you formatted that number cell with a custom 'text' only, the value will still be there to give the chart bar its 'length', but it would use the 'formatted' cell display value for the chart bar 'label'.

    zeddy

  16. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-02-20)

Page 1 of 2 12 LastLast

Posting Permissions

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