need formula for counting employees by hour

[David]

have data in one cell that shows workers hours by day 04:00-12:30 based on a
7 day work week with days off which would show as "off" or "/"
have several employees that work different hours but require so many works
on the job through out the day to complete a task which in turn need to make
sure I have sufficient workers on duty

looking for a formula on how many employees working on half hour

 

[shg]

Maybe like this:

Code
-------------------

---A--- --B-- --C-- D --E-- --F--
1 In Out Time Staff
2 Abel 14:00 22:30 04:00 2
3 Bob 08:00 16:30 04:30 2
4 Charlie 07:00 15:30 05:00 3
5 Dan 07:00 15:30 05:30 3
6 Evie 09:00 17:30 06:00 3
7 Frank 05:00 13:30 06:30 3
8 Ida 08:00 16:30 07:00 7
9 Juliet 09:00 17:30 07:30 7
10 Ken 12:00 20:30 08:00 9
11 Lenny 04:00 12:30 08:30 9
12 Mary 15:00 23:30 09:00 11
13 Nancy 10:00 18:30 09:30 11
14 Oscar 13:00 21:30 10:00 15
15 Penny 04:00 12:30 10:30 15
16 Quentin 10:00 18:30 11:00 16
17 Randy 14:00 22:30 11:30 16
18 Sam 11:00 19:30 12:00 17
19 Tom 07:00 15:30 12:30 15
20 Urie 07:00 15:30 13:00 16
21 Vic 14:00 22:30 13:30 15
22 Yanny 10:00 18:30 14:00 18
23 Zeb 10:00 18:30 14:30 18
24 15:00 19
25 15:30 15
26 16:00 15
27 16:30 13
28 17:00 13
29 17:30 11
30 18:00 11
31 18:30 7
32 19:00 7
33 19:30 6
34 20:00 6
35 20:30 5
36 21:00 5
37 21:30 4
38 22:00 4
39 22:30 1
40 23:00 1
41 23:30 0

 

[Rick Rothstein]

Can we have a little more information please? Where are you workers names...
in a row or column (which row or column would be helpful too)? Which row or
column is the worker's hours in? When does your work day start and end? For
days off... when you use a "/"... is that all that is in the cell or is
there other text combined with it? Anything else about your layout that
would affect the calculation (lunch or break times for example)? You have to
remember that when you ask a question on a newsgroup, no one here know
anything about the layout you are using, so you have to tell us, in enough
detail, so we can visualize what your set up is.

 

[David]

Workers Name are in column "a" then Sunday thru Saturday

columns b thru h the cells below the day of the week would show the shift
for each
employee such as 04:00-12:30 I wanted to populate the hours of the day below
each day of the week at 30 min time frame hope this helps

 

[Rick Rothstein]

You didn't answer all the questions I asked...

1) What time does the day's work start at and what time does the days work
end at (or do you want a 24-hour report by half-hours per day)?

2) When you use a "/" for days off, is that all that is in the cell or is
there other text coupled with it?

3) Are there any breaks in the schedule when no work is performed, such as
for lunch?

 

[David]

Sorry Rick

24 hour report Half hours

/ equals to a day the employee is not at work such as leave or training day,
Off equals employees regular day off.

Am not concerned with breaks or lunchs that is left to supervison based on
current work loads.

David

 

[Rick Rothstein]

Give this macro a try...

Sub Create15MinuteIntervals()
Dim X As Long
Dim Y As Long
Dim Z As Long
Dim LastRow As Long
Dim QuarterHours(0 To 95, 2 To 8) As Long
Dim Parts() As String
With Worksheets("Sheet1")
LastRow = .Cells(2, "A").End(xlDown).Row
For X = 2 To 8
For Y = 2 To LastRow
Parts = Split(.Cells(Y, X).Value, "-")
If UBound(Parts) = 1 Then
For Z = DateDiff("n", 0, CDate(Parts(0))) \ 15 To _
DateDiff("n", 0, CDate(Parts(1))) \ 15
QuarterHours(Z, X) = QuarterHours(Z, X) + 1
Next
End If
Next
Next
LastRow = LastRow + 2
.Rows((LastRow) & ":" & (LastRow + 96)).Clear
For X = 1 To 8
For Y = 0 To 95
If X = 1 Then
.Cells(LastRow + Y, 1).Value = Format(TimeSerial(0, 15 * Y, 0), _
"hh:mm") & " - " & Format(TimeSerial(0, 15 * Y + 14, 0), "hh:mm")
ElseIf QuarterHours(Y, X) <> 0 Then
.Cells(LastRow + Y, X).Value = QuarterHours(Y, X)
End If
Next
Next
End With
End Sub