Add Hours by Looping through Data Q

[Seanie]

I posted this in the Functions Group but it didn't progress to a
solution, I wonder is it too complicated for a function and perhaps
VBA could be a solution

Basically I am trying to add up employee's clocking for each time
segment of the day. A time segement = 1 hour eg 11:00am-12:00pm;
12:00pm-13:00pm etc etc. I have all my data in columns, each row
represent and entry for the day in question, for an employee (note I
could have several days data), so is it possible to loop through my
rows to add the total hours worked in a stated date for all employees
in each Time segment and return this value in a certain cell in my
sheet? The Date I would get from a cell I have already populated

As an example, the following would be some clocks
Emp#1 In=09:30 Out=14:30 Date=22/06/2011
Emp#2 In=10:30 Out=14:55 Date=22/06/2011
Emp#3 In= 11:30 Out=15:30 Date=22/06/2011
Emp#1 In= 16:30 Out=20:30 Date=23/06/2011
Emp#1 In= 07:30 Out=12:30 Date=24/06/2011


So for the following Hour segments on 22/06/2011 the code should
return:-

09:00-10:00 = 30mins labour hours worked
10:01-11:00 = 90mins labour hours worked
11:01-12:00 = 150min labour hours worked
12:01-13:00 = 180min labour hours worked
13:01-14:00 = 180min labour hours worked
14:01-15:00 = 145min labour hours worked
15:01-16:00 = 30min labour hours worked

 

[Paul Robinson]

Hi
How exactly is your data laid out?
1. Do you have an Employee column, In column, Out column and Date
column?
2. Is your data in one block with no empty rows?

If you have 1 and 2 above then a Pivot Table might do the job you
want. They are designed to group and count/total data.

Say exactly what your data looks like & we can go through setting up a
Pivot Table (if applicable).
regards
Paul

 

[Seanie]

Paul thanks for your reply, yes my data is laid out as you describe,
but its a dynamic table
ColA=Employee No
ColB=Date the employee worked (1 row per date worked)
ColC=ClockIn Time
ColD=ClockOut Time

So a couple of Rows of data might look like:-

98 - 22/06/2011 - 11:35 - 18:30
147 - 22/06/2011 - 07:48 - 15:35

So in time segment:-
07:00-08:30 answer I'm looking for is 12 mins
11:00-12:00 answer I'm looking for is 120 mins
etc etc covering all other hours for the day

I have Breaks etc which I will want incoroprated but if I can cross
over the first hurdle first would be great

 

[Don Guillett]

Paul thanks for your reply, yes my data is laid out as you describe,
but its a dynamic table
ColA=Employee No
ColB=Date the employee worked (1 row per date worked)
ColC=ClockIn Time
ColD=ClockOut Time

So a couple of Rows of data might look like:-

98 - 22/06/2011 - 11:35 - 18:30
147 - 22/06/2011 - 07:48 - 15:35

So in time segment:-
07:00-08:30 answer I'm looking for is 12 mins
11:00-12:00 answer I'm looking for is 120 mins
etc etc covering all other hours for the day

I have Breaks etc which I will want incoroprated but if I can cross
over the first hurdle first would be great

I thought I answered this. Enter this and copy down
'=SUMPRODUCT((HOUR($F$4:$F$300)=ROW(A1))*1)

 

[Seanie]

I thought I answered this. Enter this and copy down
'=SUMPRODUCT((HOUR($F$4:$F$300)=ROW(A1))*1)- Hide quoted text -

- Show quoted text -

How exactly does this work?

 

[Don Guillett]

How exactly does this work?

Just copy the formula into any cell and then copy down 24 rows with
the fill handle, Didn't you send me a file to dguillett1 @gmail.com

 

[Seanie]

Thanks Don, nope didn't send a file to you

I can see how it works now, only thing is, if 2 employees clockin
during an hour, your function will return 2, I'm after the total hours
worked for that hour, not the number of employees that have clocked in

 

[GS]

Seanie used his keyboard to write :

Thanks Don, nope didn't send a file to you

I can see how it works now, only thing is, if 2 employees clockin
during an hour, your function will return 2, I'm after the total hours
worked for that hour, not the number of employees that have clocked in

And so if 2 employees clock in for 1 hour (each), is that not 2 hours
worked?

 

[Seanie]

Spot on Garry, but if 1 of the employees clocks in at 9:10 and the
other at 9:30, thats a total of 80mins for the 9:00-10:00 hour, which
is the answer in that scenario I'm looking for

 

[GS]

Seanie was thinking very hard :

Spot on Garry, but if 1 of the employees clocks in at 9:10 and the
other at 9:30, thats a total of 80mins for the 9:00-10:00 hour, which
is the answer in that scenario I'm looking for

Ok, details are helful. It just seemed simple given what you stated
without elaborating. I do various projects for clients that monitor
elapsed time by project, regardless of who works on it. Thus, I
understand ( and appreciate) your attention to the finer details in
context to what you're trying to do.

 

[Peter T]

Following returns your anticipated results but needs more testing

Put your sample data in A2:C6
colA: date
colB: time In
colC: time out

If the times ever span midnight would need to adapt

Sub test()
Dim i As Long, j As Long, h As Long
Dim hIn As Single, hOut As Single
Dim mnIn As Single, mnOut As Single
Dim dFirst As Date, dLast As Date
Dim dIn As Date, dOut As Date
Dim aHour() As Date, aMins() As Long
Dim rng As Range

' in real life use a sunction to get dFirst & dLast
' ie the earlest & lates times
dFirst = #6/22/2011 8:00:00 AM#
dLast = #6/24/2011 1:00:00 PM#

' ditto about getting the source range
Set rng = Range("A2:C6")

h = (dLast - dFirst) * 24
ReDim aHour(1 To h + 1, 1 To 2)
ReDim aMins(1 To h, 1 To 1) As Long

aHour(1, 1) = dFirst
For i = 1 To h
aHour(i, 2) = aHour(i, 1) + 1 / 24
aHour(i + 1, 1) = aHour(i, 2)
Next


For i = 1 To rng.Rows.Count
dIn = rng(i, 1) + rng(i, 2)
dOut = rng(i, 1) + rng(i, 3)

hIn = (dIn - dFirst) * 24 + 1
mnIn = hIn - Int(hIn)
hIn = Int(hIn)

hOut = (dOut - dFirst) * 24 + 1
mnOut = hOut - Int(hOut)
hOut = Int(hOut)

If mnIn Then
aMins(hIn, 1) = aMins(hIn, 1) + 60 - (mnIn * 60)
hIn = hIn + 1
End If
If mnOut Then
aMins(hOut, 1) = aMins(hOut, 1) + (mnOut * 60)
hOut = hOut - 1
End If

For j = hIn To hOut
aMins(j, 1) = aMins(j, 1) + 60
Next

Next

With Range("F1:G" & h)
.Value = aHour
.NumberFormat = "hh:mm"
End With

Range("h1:H" & h).Value = aMins

End Sub


Lots of ways to reduce the unwanted hours with zero minutes, say over
evenings / WE. Eg only dump hours with non-zero minutes.


Regards,
Peter T

 

[Seanie]

^^^ Thanks for the code

It debugs on aMins(hIn, 1) = aMins(hIn, 1) + 60 - (mnIn * 60), with
Message "subscript out of range"

As you detail my data is in A2:C6

 

[Peter T]

It's still working fine for me, at least with your original test data. The
error suggests the value hIn is either 0 or above the ubound of the array.
Add these debugs after the original code lines as indicated

h = (dLast - dFirst) * 24
Debug.Print "h: "; h

hIn = Int(hIn)
Debug.Print i, "hIn: "; hIn

I get the following in the Immediate window (ctrl-g) with your data

h: 53
1 hIn: 2
2 hIn: 3
3 hIn: 4
4 hIn: 33
5 hIn: 48

Post your results

Regards,
Peter T

 

[Peter T]

To clarify, the dates and times should be date values (below in my date
format)

in A2:C6
22-06-2011 09:30 14:30
22-06-2011 10:00 14:55
22-06-2011 11:30 15:30
23-06-2011 16:30 20:30
24-06-2011 07:30 12:30

the actual values
40716 0.395833333 0.604166667
40716 0.416666667 0.621527778
40716 0.479166667 0.645833333
40717 0.6875 0.854166667
40718 0.3125 0.520833333

Do text-to-columns (space delim) and date format as required

Peter T

 

[Seanie]

Nope, still getting the Debug on same line (and nothing prints). My
Data in A2:C6 is

22/06/11 11:00 16:00
22/06/11 07:30 15:00
22/06/11 09:30 17:00
22/06/11 09:05 14:20
22/06/11 11:30 18:20


And the Code you have given + debug print above

Sub test()
Dim i As Long, j As Long, h As Long
Dim hIn As Single, hOut As Single
Dim mnIn As Single, mnOut As Single
Dim dFirst As Date, dLast As Date
Dim dIn As Date, dOut As Date
Dim aHour() As Date, aMins() As Long
Dim rng As Range


' in real life use a sunction to get dFirst & dLast
' ie the earlest & lates times
dFirst = #6/22/2011 8:00:00 AM#
dLast = #6/24/2011 1:00:00 PM#


' ditto about getting the source range
Set rng = Range("A2:C6")


h = (dLast - dFirst) * 24
Debug.Print "h: "; h
ReDim aHour(1 To h + 1, 1 To 2)
ReDim aMins(1 To h, 1 To 1) As Long


aHour(1, 1) = dFirst
For i = 1 To h
aHour(i, 2) = aHour(i, 1) + 1 / 24
aHour(i + 1, 1) = aHour(i, 2)
Next


For i = 1 To rng.Rows.Count
dIn = rng(i, 1) + rng(i, 2)
dOut = rng(i, 1) + rng(i, 3)


hIn = (dIn - dFirst) * 24 + 1
mnIn = hIn - Int(hIn)
hIn = Int(hIn)
Debug.Print i, "hIn: "; hIn



hOut = (dOut - dFirst) * 24 + 1
mnOut = hOut - Int(hOut)
hOut = Int(hOut)


If mnIn Then
aMins(hIn, 1) = aMins(hIn, 1) + 60 - (mnIn * 60)
hIn = hIn + 1
End If
If mnOut Then
aMins(hOut, 1) = aMins(hOut, 1) + (mnOut * 60)
hOut = hOut - 1
End If


For j = hIn To hOut
aMins(j, 1) = aMins(j, 1) + 60
Next


Next


With Range("F1:G" & h)
.Value = aHour
.NumberFormat = "hh:mm"
End With


Range("h1:H" & h).Value = aMins


End Sub

 

[Seanie]

To clarify, the dates and times should be date values (below in my date
format)

in A2:C6
22-06-2011 09:30 14:30
22-06-2011 10:00 14:55
22-06-2011 11:30 15:30
23-06-2011 16:30 20:30
24-06-2011 07:30 12:30

the actual values
40716 0.395833333 0.604166667
40716 0.416666667 0.621527778
40716 0.479166667 0.645833333
40717 0.6875 0.854166667
40718 0.3125 0.520833333

Do text-to-columns (space delim) and date format as required

Peter T












- Show quoted text -

Yes my actual values are the same as ^^^

40716 0.395833333 0.604166667
40716 0.416666667 0.621527778
40716 0.479166667 0.645833333
40716 0.6875 0.854166667
40716 0.3125 0.520833333

 

[Peter T]

You say "Nothing prints", but you must surely get at least the 1st debug and
the 2nd debug line must print at least once (in the first pass of the loop)

When the code breaks, in the immediate window manually debug the following
(paste each individually and hit enter)

?h
?i
?hin

what do you get

Regards,
Peter T

 

[Seanie]

Peter, bit lost here, but on 1st debug line at:- aMins(hIn, 1) =
aMins(hIn, 1) + 60 - (mnIn * 60) the following appears in the
immediate window, when I delete the contents shown and type ?h

?h
53

Then -

?i
5

Then -

?hin
0

Hope that makes sense, not sure what these figures represent

 

[Peter T]

h=53 is the number of hours between the dFirst and dLast times, and the size
the arrays have been dimensioned to.

i=5 means things are failing on the last loop while looking at the 5 rows of
data, ie looking at row 6

hin=0 ' (it should be 48)
is the result of
dIn = A6+B6 ' 5th loop means data in row 6
hIn = dIn-dFirst

So, what values do you have in A6 & B6? Are you sure the first row of data
is in row2 and not perhaps in row1


Regards,
Peter T

 

[Seanie]

A6 = 22/06/2011
B6 = 07:30

Has it got to do with the fact that Row6 has a start time earlier than
Row1? Real life, that could be the case, as Data would be sorted by
Emplee No, and Row6 Employee might be # 100, whereas Row2 might be #15
etc etc