# Thread: Excel - Tracking Labor Hours

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

4. 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. 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. 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. 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

8. 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. 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. Originally Posted by sdckapr
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. How about something like this:

Chart1.png

Chart3.png

HTH,
Maud

12. Originally Posted by Maudibe

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. Hi Maud

But I couldn't help myself (as RG would say) to modify it a bit.
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

14. 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. 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 Last

#### Posting Permissions

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