Count number of employees on duty during any given 1 hour timeframe of the day

[Coffee_in_a_jiffy]

I have a problem I'm trying to solve in Google Sheets. I have a column of employee names; a column with their "clock-in" time; a column with their "clock-out" time. I also have a column with each 1 hour segment of the day in each cell starting "5-6 am" then "6-7 am" and so on.
In the next column I want to be able to count how many employees were on-duty during each 1 hour segment of the work day. Example: "5-6 am" there were 2 employees working; between "6-7 am" there were three employees working, and so on throughout the day.
I have tried using the countif and countifs functions and cannot seem to make this work.

Thank you in advance!

 

[ranman256]

something like this...

Sub ParseHrs()
Dim mvMinHr, mvMaxHr
Dim vTim As Date, vStart As Date, vEnd As Date
Dim iHr As Integer, i As Integer, iStartHr As Integer, iEndHr As Integer, iCt As Integer
Dim colCt As New Collection
Dim sKey As String

mvMaxHr = 0
mvMinHr = 23

Range("B2").Select
While ActiveCell.Value <> ""
   vTim = ActiveCell.Value
   iHr = Hour(vTim)
   If iHr < mvMinHr Then mvMinHr = iHr
   
   vTim = ActiveCell.Offset(0, 1).Value
   iHr = Hour(vTim)
   If iHr > mvMaxHr Then mvMaxHr = iHr
   
  ActiveCell.Offset(1, 0).Select  'next row
Wend


Range("a16").Select
For i = mvMinHr To mvMaxHr
   sKey = i & "-" & i + 1
   ActiveCell.Offset(0, 0).Value = "'" & sKey
   ActiveCell.Offset(0, 2).Value = i & ":00"
   ActiveCell.Offset(1, 0).Select  'next row
   
   colCt.Add 0, sKey
Next



'now count
Range("B2").Select
While ActiveCell.Value <> ""
   vStart = ActiveCell.Offset(0, 0).Value
   vEnd = ActiveCell.Offset(0, 1).Value
   
   iStartHr = Hour(vStart)
   iEndHr = Hour(vEnd)
   i = iStartHr
   
   iHr = DateDiff("h", vStart, vEnd)   'hrs worked
   While i < iEndHr
      sKey = i & "-" & i + 1
      iCt = colCt(sKey)
      iCt = iCt + 1
      colCt.Remove sKey
      colCt.Add iCt, sKey
      i = i + 1
   Wend
   
  ActiveCell.Offset(1, 0).Select  'next row
Wend

'now post final count
Range("A16").Select
While ActiveCell.Value <> ""
   sKey = ActiveCell.Value
   ActiveCell.Offset(0, 1).Value = colCt(sKey)
   
   ActiveCell.Offset(1, 0).Select  'next row
Wend

set colCt = nothing  'clear memory
MsgBox "done"
End Sub

 

[Coffee_in_a_jiffy]

Wow! that's incredible! Thank you, but I'm not nearly advanced enough to know what to do with that code. That code doesn't look like any formula that I've been working with.

 

[Coffee_in_a_jiffy]

ok. so i did a bit of looking around and figured out this is a script code. lol
I get this error when saving the code: Syntax error: SyntaxError: Unexpected identifier line: 1 file: Code.gs