I posted this a few weeks ago, as i was working with the data, i noticed that some of the hourly calculations were not correct. To shed some light on this, here is what i am trying to do:
I have the following headers:
A=Payroll Company
B=Code Payroll Name
C=File Number
D=Payroll Pay Date
E=Timecard Hours
F=Paycode
[TABLE="width: 1070"]
<tbody>[TR]
[TD="class: xl65, width: 156"]XWT[/TD]
[TD="class: xl65, width: 201"]Adams III, Jon[/TD]
[TD="class: xl65, width: 96"]009944[/TD]
[TD="class: xl66, width: 166"]08/31/2018[/TD]
[TD="class: xl67, width: 299"]4.50[/TD]
[TD="class: xl65, width: 152"]REGULAR
[/TD]
[/TR]
</tbody>[/TABLE]
Our work schedule starts on Monday and ends Sunday.
The example above is for one day, let say this employee worked 4 days, Monday Tuesday, Friday and Saturday, you would see 3 other rows with similar information except a different amount of hours worked.
What i was trying to do is add the total hours worked for that week.
This script was given to me by MickG, unfortunately, its calculating hours in some slots differently.
This was for one user so far, this is what her schedule looked like:
She worked according to the hours in her time slot a total of 37.2 hours from 01/01/2018 til 01/07/2018.
[TABLE="class: cms_table, width: 1070"]
<tbody>[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/01/2018[/TD]
[TD]5.20[/TD]
[TD]REGULAR[/TD]
[/TR]
[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/02/2018[/TD]
[TD]8.20[/TD]
[TD]REGULAR[/TD]
[/TR]
[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/03/2018[/TD]
[TD]7.50[/TD]
[TD]REGULAR[/TD]
[/TR]
[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/04/2018[/TD]
[TD]8.00[/TD]
[TD]REGULAR[/TD]
[/TR]
[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/07/2018[/TD]
[TD]8.30[/TD]
[TD]REGULAR[/TD]
[/TR]
</tbody>[/TABLE]
The code calculated it as 28.9 hours.
Any idea where i can make edits to the code to fix this?
I have the following headers:
A=Payroll Company
B=Code Payroll Name
C=File Number
D=Payroll Pay Date
E=Timecard Hours
F=Paycode
[TABLE="width: 1070"]
<tbody>[TR]
[TD="class: xl65, width: 156"]XWT[/TD]
[TD="class: xl65, width: 201"]Adams III, Jon[/TD]
[TD="class: xl65, width: 96"]009944[/TD]
[TD="class: xl66, width: 166"]08/31/2018[/TD]
[TD="class: xl67, width: 299"]4.50[/TD]
[TD="class: xl65, width: 152"]REGULAR
[/TD]
[/TR]
</tbody>[/TABLE]
Our work schedule starts on Monday and ends Sunday.
The example above is for one day, let say this employee worked 4 days, Monday Tuesday, Friday and Saturday, you would see 3 other rows with similar information except a different amount of hours worked.
What i was trying to do is add the total hours worked for that week.
This script was given to me by MickG, unfortunately, its calculating hours in some slots differently.
HTML:
Sub MG15Mar48Dim n As Long
Dim Rng As Range
Dim Ray As Variant
Dim Dic As Object
Dim Q As Variant
Dim Wnum As Long
Ray = Range("A1").CurrentRegion
Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
For n = 2 To UBound(Ray, 1)
Wnum = Application.WeekNum(Ray(n, 4))
If Not Dic.exists(Ray(n, 3)) Then
Set Dic(Ray(n, 3)) = CreateObject("Scripting.Dictionary")
End If
If Not Dic(Ray(n, 3)).exists(Wnum) Then
Dic(Ray(n, 3)).Add (Wnum), Array(Ray(n, 2), Ray(n, 5))
Else
Q = Dic(Ray(n, 3)).Item(Wnum)
Q(1) = Q(1) + Ray(n, 5)
Dic(Ray(n, 3)).Item(Wnum) = Q
End If
Next n
Dim k As Variant
Dim p As Variant
Dim c As Long
ReDim nray(1 To UBound(Ray, 1), 1 To 4)
c = 1
nray(1, 1) = "Payroll Name": nray(1, 2) = "File Number"
nray(1, 3) = "Week Num": nray(1, 4) = "Timecard Hours"
For Each k In Dic.Keys
For Each p In Dic(k)
c = c + 1
nray(c, 1) = Dic(k).Item(p)(0)
nray(c, 2) = k
nray(c, 3) = p
nray(c, 4) = Dic(k).Item(p)(1)
Next p
Next k
With Sheets("Sheet2").Range("A1").Resize(c, 4)
.Value = nray
.Borders.Weight = 2
.Columns.AutoFit
.HorizontalAlignment = xlCenter
End With End Sub
This was for one user so far, this is what her schedule looked like:
She worked according to the hours in her time slot a total of 37.2 hours from 01/01/2018 til 01/07/2018.
[TABLE="class: cms_table, width: 1070"]
<tbody>[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/01/2018[/TD]
[TD]5.20[/TD]
[TD]REGULAR[/TD]
[/TR]
[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/02/2018[/TD]
[TD]8.20[/TD]
[TD]REGULAR[/TD]
[/TR]
[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/03/2018[/TD]
[TD]7.50[/TD]
[TD]REGULAR[/TD]
[/TR]
[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/04/2018[/TD]
[TD]8.00[/TD]
[TD]REGULAR[/TD]
[/TR]
[TR]
[TD]XWT[/TD]
[TD]Mary J[/TD]
[TD]008126[/TD]
[TD]01/07/2018[/TD]
[TD]8.30[/TD]
[TD]REGULAR[/TD]
[/TR]
</tbody>[/TABLE]
The code calculated it as 28.9 hours.
Any idea where i can make edits to the code to fix this?
Last edited: