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" wrote in message
...
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