Staff resource query

M

MJKelly

Hi,

How can I use the following input to populate the "Staff Count" as
shown in the example below.

Start Finish
Person1 06:00 08:00
Person2 07:00 09:30


Time Staff Count
06:00 1
06:10 1
06:20 1
06:30 1
06:40 1
06:50 1
07:00 2
07:10 2
07:20 2
07:30 2
07:40 2
07:50 2
08:00 1
08:10 1
08:20 1
08:30 1
08:40 1
08:50 1
09:00 1
09:10 1
09:20 1
09:30 1

kind regards,
Matt
 
T

Tom Hutchins

Maybe something like...

=SUMPRODUCT(--($B$2:$B$3<=A7),--($C$2:$C$3>=A7))

where 6:00 is in A7. Copy down as needed. Please note there is an
inconsistency in the sample output you gave. You don't include Person1 at
8:00, but you do include Person2 at 9:30. To be consistent, you should either
show 2 at 8:00 and 1 and 9:30, or 1 at 8:00 and 0 at 9:30. The formula above
will give 2 at 8:00 and 1 and 9:30. To get 1 at 8:00 and 0 at 9:30, change
the formula to

=SUMPRODUCT(--($B$2:$B$3<=A8),--($C$2:$C$3>=A8))

Hope this helps,

Hutch
 
J

Joel

This is a little complicated to handle all the odd ball cases

Sub Staff_Count()

With Sheets("Sheet1")
'find eariest time
Sh1RowCount = 2
First = True
Do While .Range("B" & Sh1RowCount) <> ""
If First = True Then
Earliest = .Range("B" & Sh1RowCount)
Latest = .Range("C" & Sh1RowCount)
Else
If .Range("B" & Sh1RowCount) < Earliest Then
Earliest = .Range("B" & Sh1RowCount)
End If
If .Range("C" & Sh1RowCount) > Latest Then
Latest = .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, EarliestMinute, 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)
EndTime = .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
 
T

Tom Hutchins

To create the times in 10-minute increments, enter the earliest start time in
A7 (for example). In A8, enter

=A7+TIMEVALUE("0:10:00")

and copy down until you have reached the latest finish time. Format the
cells to look like 6:00, etc.

Hutch
 
D

dan dungan

Hi Joel,

I think I dimmed something wrong.

Sub Staff_Count()
Dim Sh1RowCount As Integer
Dim Sh2RowCount As Integer
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 EarliestMinute As Double
Dim LatestHour As Double
Dim LatestMinute As Double
Dim TenMinute As Double
Dim TimeCount As Integer


The code returns the error "Run Time 6 overflow" at

Sh2RowCount = Sh2RowCount + 1

Sh2RowCount shows 32767

Please help clarify

Thanks,

Dan
 
J

Joel

Rows should be longs, and time should be a double. but you should not be
getting 32,767 rows for Sheet 2 Row counts. For one day time period you
should have a maximum of 24 hours times 6 (10 minute intervals per hour) =
144. The only way this can happen is if your Dates on sheet 1include the
date.

Time in excel starts at Jan 1, 1900 whith every day equaling one and every
hour equaling 1/24. The code need some minor chages to strip out the days
from the time.


Sub Staff_Count()

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 EarliestMinute 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, EarliestMinute, 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
 
D

dan dungan

Thanks for your feedback, Joel.

Your changes processed with no errors.

This is what I had in Sheet 1:

Employee Start Finish
Dan 8:00 AM 11:00 AM
Rick 9:00 AM 1:00 PM
bob 5:00 AM 2:00 PM
john 5:00 PM 11:00 PM
linda 1:00 PM 6:00 PM
borga 7:00 PM 11:50 PM

I don't understand how you determined what was causing the error.

Dan
 
J

Joel

Because there aren't 32,000 10 minute period in 24 hours. The only way the
code could of gotten to 32,000 was if you had more than one day.

If you want to run a quick test then format Column A for date and time.
Highlght Column A and go to Format - Numbers - Date and choose a format the
has both days and Time and you will see how the Dates are stored in Excel.

You will probably see some with the year 1900 and some with the year 2008.
 
D

dan dungan

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
 
J

Joel

I t took me 30 seconds to fix the 2 problems. Not sure why the code worked
when I tested it???? See comments below where I made the 2 changes.


Sub Staff_Count()

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 EarliestMinute 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 '<= added somehow got dropped
Earliest = .Range("B" & Sh1RowCount) - _
Int(.Range("B" & Sh1RowCount))
Latest = .Range("C" & Sh1RowCount) - _
Int(.Range("C" & Sh1RowCount))
First = False
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) - _
Int(.Range("C" & Sh1RowCount)) '<= Lost INT
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, EarliestMinute, 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Write a Macro to retain rows based on certain criteria 1
Find then highlight in yellow 6
Add Hours by Looping through Data Q 27
Calculating occupational sick pay entitlement 0
macro adding rows 8
Help w/Duplicates & Macros 5
Adding times 1
Calculation of Overlap Outage Hours 17

Top