Hi Joel,
I've stepped through the code several times with the locals window
open to try to understand the logic.
I'm not sure what I've done to make the code not work as expected.
The macro doesn't seem to be returning all the data.
When I run the process, the output starts at 7:00 pm.
I was expecting to see output beginning at 5:00 am.
Thanks for your feedback and time.
Dan
____________________________________________________
After formatting per your recommended test, the data in sheet1, cells
A1:C7, looks like this:
Employee Start Finish
Dan 1/0/00 8:00 AM 1/0/00 11:00 AM
Rick 1/0/00 9:00 AM 1/0/00 1:00 PM
bob 1/0/00 5:00 AM 1/0/00 2:00 PM
john 1/0/00 5:00 PM 1/0/00 11:00 PM
linda 1/0/00 1:00 PM 1/0/00 6:00 PM
borga 1/0/00 7:00 PM 1/0/00 11:50 PM
The output on sheet2 A1:B31, looks like this:
Time Staff Count
19:00 2
19:10 2
19:20 2
19:30 2
19:40 2
19:50 2
20:00 2
20:10 2
20:20 2
20:30 2
20:40 2
20:50 2
21:00 2
21:10 2
21:20 2
21:30 2
21:40 2
21:50 2
22:00 2
22:10 2
22:20 2
22:30 2
22:40 2
22:50 2
23:00 2
23:10 1
23:20 1
23:30 1
23:40 1
23:50 1
Here's the code I used.
Dim Sh1RowCount As Long
Dim Sh2RowCount As Long
Dim First As Boolean
Dim Earliest As Double
Dim Latest As Double
Dim StartTime As Double
Dim EndTime As Double
Dim EarliestHour As Double
Dim EarlietMinute As Double
Dim LatestHour As Double
Dim LatestMinute As Double
Dim TenMinute As Double
Dim TimeCount As Double
With Sheets("Sheet1")
'find eariest time
Sh1RowCount = 2
First = True
Do While .Range("B" & Sh1RowCount) <> ""
If First = True Then
Earliest = .Range("B" & Sh1RowCount) - _
Int(.Range("B" & Sh1RowCount))
Latest = .Range("C" & Sh1RowCount) - _
Int(.Range("C" & Sh1RowCount))
Else
If .Range("B" & Sh1RowCount) < Earliest Then
Earliest = .Range("B" & Sh1RowCount) - _
Int(.Range("B" & Sh1RowCount))
End If
If .Range("C" & Sh1RowCount) > Latest Then
Latest = .Range("C" & Sh1RowCount) - _
(.Range("C" & Sh1RowCount))
End If
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With
'earliest round down to nearest 10 minutes
EarliestHour = Hour(Earliest)
EarlietMinute = Minute(Earliest)
EarlietMinute = 10 * Int(EarlietMinute / 10)
Earliest = TimeSerial(EarliestHour, EarlietMinute, 0)
'Latest round up to nearest 10 minutes
LatestHour = Hour(Latest)
LatestMinute = Minute(Latest)
If LatestMinute Mod 10 <> 0 Then
LatestMinute = 10 * Int(LatestMinute / 10)
If LatestMinute = 50 Then
LatestMinute = 0
LatestHour = LatestHour + 1
End If
End If
Latest = TimeSerial(LatestHour, LatestMinute, 0)
'create time
With Sheets("Sheet2")
.Columns("A").NumberFormat = "hh:mm"
TenMinute = 1 / (24 * 6)
.Range("A1") = "Time"
.Range("B1") = "Staff Count"
Sh2RowCount = 2
For TimeCount = Earliest To Latest Step TenMinute
.Range("A" & Sh2RowCount) = TimeCount
Sh2RowCount = Sh2RowCount + 1
Next TimeCount
End With
With Sheets("Sheet1")
'find eariest time
Sh1RowCount = 2
Do While .Range("B" & Sh1RowCount) <> ""
StartTime = .Range("B" & Sh1RowCount) - _
Int(.Range("B" & Sh1RowCount))
EndTime = .Range("C" & Sh1RowCount) - _
Int(.Range("C" & Sh1RowCount))
Sh2RowCount = 2
With Sheets("Sheet2")
Do While .Range("A" & Sh2RowCount) <> ""
Select Case .Range("A" & Sh2RowCount)
Case Is > EndTime
Exit Do
Case Is >= StartTime
.Range("B" & Sh2RowCount) = _
.Range("B" & Sh2RowCount) + 1
End Select
Sh2RowCount = Sh2RowCount + 1
Loop
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub