PDA

View Full Version : Labour Analysis



DerekS
2017-11-30, 09:06 PM
Hello All,

I have been trying to figure out a better way to analyze my labour but I keep running into the same issue. The way that my data is extracting from my labour tool is as follows:



Employee Name
Date
TimeIn
TimeOut
TotalHours


John Doe
Mon 7/31/17
5:00pm
8:00pm
3


Jane Doe
Tue 8/1/17
6:00pm
1:09am
7.15



A couple things:

our total hours round up to the nearest 15min increment
we want to ensure that all labour between 12:00am and 4:00am gets coded to the previous day.


We have set up a very complicated formula to try and analyze the number of labour hours in each hourly time period (ie 12 labour hours during 5:00pm). Part of the problem is that, given the dataset above, we don't know how to set up a formula/macro so that will show us the hourly timeslots that the employee worked in. I would like to be able to transform the data so that it comes in the following format:



Employee Name
Date

Hour(24clck)
HoursWorked


John Doe

Mon 7/31/17
17
1


John Doe
Mon 7/31/17
18
1


John Doe
Mon 7/31/17
19
1


Jane Doe
Tue 8/1/17
18
1


Jane Doe
Tue 8/1/17
19
1


Jane Doe
Tue 8/1/17
20
1


Jane Doe
Tue 8/1/17
21
1


Jane Doe
Tue 8/1/17
22
1


Jane Doe
Tue 8/1/17
23
1


Jane Doe
Tue 8/1/17
24
1


Jane Doe
Tue 8/1/17
1
.15



With this information in this format, we can easily run a pivot to figure out how many total labour hours were included in each hour of the day.

Is there a good way for me to run a macro against the original dataset so that I can see my information as shown in Table2? Thanks in advance for your help!

Derek

p45cal
2017-12-01, 12:48 PM
A couple things:

our total hours round up to the nearest 15min increment
we want to ensure that all labour between 12:00am and 4:00am gets coded to the previous day.


What to do in the following example:
AJ clocks in at 3:29 am, that's just over half an hour before the end of the day at 4am.
He clocks out at 4:31 am, so he's worked just over half an hour of the next day.
His total time worked is 1 hour and 2 minutes.
That's rounded up to 1 and a quarter hours.
How do you distribute the the extra 13 minutes between the two days?
No rounding here yet:
7653
(here I'm seeing a tiny image, click it to see a bigger one)

Hercules1946
2017-12-02, 01:23 PM
@P45cal
Its a good example of how its necessary to provide for all logical eventualities in designing a robust system. Your example is probably going to be a rare occurrence, so its unlikely to skew any stats derived from the data. Off the cuff, I would say add the quarter hour to the day clocked out, as its more likely that the extra time was spent working when a person leaves late. :)

Bob Phillips
2017-12-03, 12:09 AM
I would have thought it better to roundup the clock-in and clock-off times, so that neither party is always disadvantaged.

Hercules1946
2017-12-03, 10:03 PM
I would have thought it better to roundup the clock-in and clock-off times, so that neither party is always disadvantaged.
Wouldn't that result in a reduction in the total hours paid if you round up the clock in times ? At present, the figure is ROUNDUP(Clock Out - Clock in).

DerekS
2017-12-04, 06:15 PM
Thanks for the quick reply @p45cal.

It is VERY rare for this scenario to occur as most staff are signed out by 2am. It is even more unlikely that a staff member would sign in before 4am and continue working the rest of that day. Perhaps the best logic to set up in the formula is that any shift that starts before 4am should be included in the previous day's labour.

Either way, we would likely scrub this data and manually eliminate any outliers such as the one suggested above.

DerekS
2017-12-04, 06:21 PM
Wouldn't that result in a reduction in the total hours paid if you round up the clock in times ? At present, the figure is ROUNDUP(Clock Out - Clock in).

Thanks Hercules1946.

This analysis is for internal use only and would not be used in the payment of our employees. Employees are paid to the minute. Our use for this data is to see how many hours are being logged in each hourly segment of the day. Rounding will make this data a little cleaner.

Bob Phillips
2017-12-04, 08:16 PM
Wouldn't that result in a reduction in the total hours paid if you round up the clock in times ? At present, the figure is ROUNDUP(Clock Out - Clock in).
Surely you can see that sometimes the clockin time would roundup, sometimes the clockout. Over time they would even out. Of course, you might find some cut character who played the system, came in at 10:00 say and clocked out regularly at say 16:01(2,3,...), but would the gain be worth the faff.

p45cal
2017-12-05, 05:50 PM
I'm getting there; can you supply us with a workbook with stack of real data in (do a search-and-replace on names to obfuscate real names, but don't do it wholesale so that you reduce the number of different names)?

Hercules1946
2017-12-05, 10:52 PM
Surely you can see that sometimes the clockin time would roundup, sometimes the clockout.

Yes, I can see that. The point I was making is that if you round up a clock in time this means that you are not including the time between the clock in time and its rounded up time.
If I clock in at 7:46 and out at 16:10, then I've worked 8 hrs 24 mins, which is 8 hrs 30 mins rounded up. If I round up the start time to 8:00, I've worked 8hrs 10mins, rounded up to 8 hrs 15mins, i.e. 15mins less.
As the OP has said these hours are not being used for payroll purposes (which I thought they were) then it probably doesn't matter, but I'm still of the opinion that one rounding of the total hours is more accurate.

DerekS
2017-12-08, 07:19 PM
Here is the dataset.

I have removed names and added employee numbers (not actual numbers). I have also removed the job names and added codes.

We have created a model for this but we cannot pivot the current model.

p45cal
2017-12-09, 12:23 AM
The first thing to point out is that the dates in column C of your file are not real Excel dates.
In the attached there's a macro blah whose first job is to convert those dates, then it goes about creating a new table on a new sheet.
That macro is called when you click the button on Sheet1.
The 2nd column of the new table contains an hourly date/time representing the beginning of the hour that that row's data refer to.
The 3rd column gives duration in decimal hours - this is not rounded; I don't think it needs to be.
The 4th column is to ascribe hours in the morning before 4am to the previous day - so you can use ascribed date in your pivots.
The 5th column show the day of the week of that ascribed date.

It takes a few minutes here to produce the new table. If you get bored waiting, come back and we should be able to speed it up considerably.
I've included a sheet with a new table and thrown in a sample pivot table and chart. The chart shows the number of hours on the vertical axis and the hour of the day along the bottom.
I've left it showing all employees for all the days of the week, but you can use the slicers to give you only a subset of the days of the week and a subset of employees.
Each bar of the chart shows the sum of the hours that the employee(s) worked on that/those day(s) of the week.

Right, I can't attach the file here, it's too big, so here's a link to it:
https://app.box.com/s/3ttxlmzg4e9p0tteke0bbj9smei0u8t2

and I'll include the code that works on your most recent attachment:

Sub blah()
Application.ScreenUpdating = False
With Sheets("Sheet1")
'Convert dates to real dates:
Set myRng = .Cells(1).CurrentRegion.Columns(3)
Set myRng = Intersect(myRng, myRng.Offset(1))
myRng.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True, FieldInfo:=Array(Array(1, 9), Array(2, 3)) 'this line will error if the dates have already been converted.

'make the new table:
Set Destn = Sheets.Add(after:=Sheets(Sheets.Count)).Range("A2")
Destn.Offset(-1).Resize(, 6).Value = Array("Employee #", "DateTime", "Duration", "Ascribed Date", "Hr of the day", "Day Name")
For Each cll In .Range("C2:C" & .Cells(.Rows.Count, 3).End(xlUp).Row)
'cll.Select
TimeIn = cll.Value + cll.Offset(, 1).Value
TimeOut = cll.Value + cll.Offset(, 2).Value
If cll.Offset(, 1).Value > cll.Offset(, 2).Value Then TimeOut = TimeOut + 1
A1 = TimeIn: A2 = TimeOut
For hr = Application.WorksheetFunction.Floor_Precise(TimeIn, 1 / 24) To Application.WorksheetFunction.Ceiling_Precise(TimeOut, 1 / 24) Step 1 / 24
B1 = hr: B2 = hr + 1 / 24
Overlap = Application.Max(0, Application.Min(A2, B2) - Application.Max(A1, B1))
'Debug.Print CDate(Overlap)
If Overlap > 0.000003 Then
'Destn.Select
Destn.Value = cll.Offset(, -2).Value 'employee name/#
Destn.Offset(, 1).Value = hr 'hourly date time
Destn.Offset(, 1).NumberFormat = "dd mmm yyyy hh:mm"
Destn.Offset(, 2).Value = Overlap * 24 'duration in decimal hours (up to one hour)
'Destn.Offset(, 2).NumberFormat = "[h]:mm"
Destn.Offset(, 3).Value = Int(hr - 4 / 24) 'ascribed date (early hours are ascribed to previous day)
Destn.Offset(, 3).NumberFormat = "dd mmm yyyy"
Destn.Offset(, 4).Value = Round((hr - Int(hr)) * 24, 0) Mod 24 'the hour of the day (start of)
Destn.Offset(, 5).Value = Format(Int(hr - 4 / 24), "ddd") 'day of the week
Set Destn = Destn.Offset(1) 'move on to next row on sheet
End If
Next hr
Next cll
End With
Application.ScreenUpdating = True
End Sub

Hercules1946
2017-12-10, 01:24 PM
In the attached there's a macro blah whose first job is to convert those dates, then it goes about creating a new table on a new sheet.
That macro is called when you click the button on Sheet1.


Great piece of work!
The only thing I'd change would be to find a more distinguished name for this impressive set of code. :clap2:

DerekS
2017-12-12, 05:04 PM
Wow! This website is amazing. Our team will try to work through this and see what comes of it. Thank you so much for your efforts!