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

Coffee_in_a_jiffy

New Member
Joined
Sep 30, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • 2022-09-30.png
    2022-09-30.png
    81.7 KB · Views: 9

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
something like this...

Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,323
Messages
6,171,468
Members
452,406
Latest member
kken tthompson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top