Count employees working during certain hourly ranges

[cohoonma]

I am trying to figure out the following. I have a list of employees that have a start-time and end-time in cells.

I want to try and calculate the number of employees that work during:
1. 8am-11am
2. 8am-5pm
3. 5pm-10pm
An example of the data is:

   A             B        C
1  John Doe      Start    08:00 am
2                End      05:00 pm
3  Jane Doe      Start    08:00 am
4                End      10:30am
.
.
.

I think I may have to do something like this:
Set range = C1:Cxx (xx is the end of the range)
set counter = 0
for each cell in the range
if cell.value > "08:00 am" and cell.value < "11:00 am"
counter = counter + 1
set morningshift cell value to counter
end if
next

for each cell in the range
if cell.value > "08:00 am" and cell.value < "5:00 pm"
counter = counter + 1
set afternoonshift cell value to counter
end if
next
for each cell in the range
if cell.value > "5:00 pm" and cell.value < "10:00 pm"
counter = counter + 1
set eveningshift cell value to counter
end if
next

I am just not having much luck putting this into the right code. I though about using the Excel builtin function COUNTIF, but that just started to get extrememly complicated, so I thought a bit of code would work better.

Can anyone lend some assistance and let me know if I am going dopwn the right path as well, or suggest a better way?

Lastly, how do I make the code execute automatically? I don't care if it runs each time data is entered into a cell, I just don't really want the person to have to press a button to initiate it.
thanks,
Michael

 

[SkipVought]

Hi,

The task would be alot simpler if your data were organized in a tabulat format like...
[tt]
Employee Start End
John Doe 8:00 AM 5:00 PM
Jane Doe 8:00 AM 10:30 AM
[/tt]
then you build a table of ranges you want to test for...
[tt]
strt fin
8:00 11:00
11:00 17:00
17:00 22:00
[/tt]
then your counts...
[tt]
=SUMPRODUCT((F2<=End)*( G2>=Start))
[/tt]
where colum F is strt and column G is fin




Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blown-apart!}

 

[cohoonma]

Skip, I tried out you idea and it works, unfortunately in my situation, I have to have it laid out the way I had it in the orginal post. I have names going down with a column for start and end times for Sunday - Saturday. I am duplicating what a company is using right now, only it is a paper based corporate thing. So changing things around won't work.

I tried setting up a named range called SundayStart which only contained start times. The cells were not contiguous, they skipped every other one. So my start range would equal sheet1.c7, sheet1.c9,...,sheet1.c33. My SundayEnd range would be sheet1.c2, sheet1.c4,...,sheet1.c34.

I would then set up named ranges for MondayStart using column D, TuesdayStart using column E, etc. Same for end ranges.

I am guessing the SUMPRODUCT function has an issue with non-contiguos cells as I get a #VALUE! error when I plug my range nme into the formula. The named ranges work if I use contiguous cells, such as in the layout you sggested.

I am playing around with it to see if I can somehow make it work.

Thanks for the suggestions,
Michael

 

[SkipVought]

Michael,

Unfortunately, when you violate sound spreadsheet table design principles, speadsheet function fail to function.

One of the biggest mistakes I've seen time and again (even in big corporations), have been duplication a non-computer or paper process without proper analysis and design that is required to leverage the functionality of computers, in this case, spreadsheets.

What you want to do will be extremely difficult with you current data organization.

BTW, another issue is that TIME VALUES are really NUBERS and not text strings, so this statement will never work

if cell.value > "08:00 am" and cell.value < "11:00 am" then

What would work is

if cell.value > #08:00 am# and cell.value < #11:00 am# then

So I'll take a stab at manipulating your data. Hold on.


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blown-apart!}

 

[SkipVought]

Sub test()
   Dim t1(2, 1)
   Const SRT_ = 0
   Const END_ = 1
   t1(0, SRT_) = #8:00:00 AM#
   t1(0, END_) = #10:59:00 AM#
   t1(1, SRT_) = #11:00:00 AM#
   t1(1, END_) = #4:59:00 PM#
   t1(2, SRT_) = #5:00:00 PM#
   t1(2, END_) = #9:59:00 PM#
   
   Set rng = Range(Cells(1, 3), Cells(1, 3).End(xlDown))
   cnt = 0
   
   For Each r In rng
      With r
         Select Case .Offset(0, -1).Value
            Case "Start"
               tStart = .Value
            Case "End"
               tEnd = .Value
               For i = 0 To UBound(t1, 1)
                  x = 0
                  y = 0
                  For j = 0 To UBound(t1, 2)
                     Select Case j
                        Case SRT_
                           If Format(tEnd, "hh:mm") > Format(t1(i, j), "hh:mm") Then
                              x = 1
                           End If
                        Case END_
                           If Format(tStart, "hh:mm") < Format(t1(i, j), "hh:mm") Then
                              y = 1
                           End If
                     End Select
                  Next
                  cnt = cnt + x * y
               Next
         End Select
      End With
   Next
   MsgBox cnt
End Sub

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blown-apart!}

 

[cohoonma]

Wow, I am amzed at your depth of knowledge to crank out that bit of code. It is going to take me some time to really step through it to understand it but I have to give you a big thanks!!

Don't give me another thought now.

Michael