Help with Script, error in hourly calculations

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
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.

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:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top