Counting the number of working staff during specific time periods

[Jen_Sova]

Hello. I am using excel for to schedule staff. I am an advanced beginner with functions and formulas. What would be the best option to count how many staff are working in 4 hour increments?

 This screen shot is how the schedule is entered, with a start and end time. I have 62 employees

 This is the 4 hr increments that I would like a count of employees scheduled.

 This is the formula I am using? Does this look correct? My counts correlate with the exception of my issue below.

I am having difficulty with the count between 11pm and 3am. I also have errors in my count on the days a person is scheduled to work 1230pm to 1am. This formula is adding that person to all of the 4 hours increments. Any suggestions?

[#2]

I have more questions than answers right now:
a) when you want a count for that time range is that anyone that works at all during that period or work for the entire period?
works at any point: ([start time]<=[period end])*([end time]>=[period start])
works whole period: ([start time]<=[period start])*([ent time]>=[period end])
b) how are those start and end times entered? I assume they are not text but are they just time? So wouldn't 23:00 be after 7:00? or is the date included so 23:00 is really 3/9/2024 23:00?
c) you broke out the start and end times nicely on the schedule why no on the periods so you can use a formula to easily grab the start and end of the desired periods?
d) you say it is working but the formula you give doesn't make sense to me that it would work because it is looking at only 1 column.
e) you list the time ranges starting at 7am and then going through 11p-3a and 3a-7a. Are those last 2 both the times going into the next day but added to the prior day? or is 3a-7a this day's morning shift just in a weird order. And then what about the 11p-3a, would that be either after 11p today AND/OR before 3am today?
f) if you are 'lapping' days then you need some rules about how to treat those values or better yet enter them so they calc correctly. for example 1a the next day could be 25:00 or have the date tied to the time.

[#3]

@m_tarler Thank you for responding. 

a) when you want a count for that time range is that anyone that works at all during that period or work for the entire period? It would be for the entire period. 
works at any point: ([start time]<=[period end])*([end time]>=[period start])
works whole period: ([start time]<=[period start])*([ent time]>=[period end])
b) how are those start and end times entered? I assume they are not text but are they just time? So wouldn't 23:00 be after 7:00? or is the date included so 23:00 is really 3/9/2024 23:00? Start and end times are entered using just time, no date. 
c) you broke out the start and end times nicely on the schedule why no on the periods so you can use a formula to easily grab the start and end of the desired periods? I hadn't thought of that. What would that formula look like?
d) you say it is working but the formula you give doesn't make sense to me that it would work because it is looking at only 1 column. I haven't that faintest idea why it's working but the count is correct?? I am certain it is probably a fluke/coincidence.
e) you list the time ranges starting at 7am and then going through 11p-3a and 3a-7a. Are those last 2 both the times going into the next day but added to the prior day? Correct, our 24 hour period runs from 7am - 7am or is 3a-7a this day's morning shift just in a weird order. And then what about the 11p-3a, would that be either after 11p today AND/OR before 3am today? It would be 11pm today and/or before 3am tomorrow.
f) if you are 'lapping' days then you need some rules about how to treat those values or better yet enter them so they calc correctly. for example 1a the next day could be 25:00 or have the date tied to the time. This is what I needed to know. Is there a clean way to tie the date to the time without entering it into the cell itself. I am open to any advice!

[#4]

If you can share an example worksheet (no personal info) it would be easier for us to see and help. If you can't attach here you could upload to cload and provide link or PM it to me.
the answer to (c) is either using a formula like I showed in question (a) or with a COUNTIFS(D4:D62,">="&B2) you just put the cell ref into the formula
for (e)/(f) I would try something like this: =A3-TIME(7,0,0)+(A3-TIME(7,0,0)<0) for all time values to shift them by 7 hours so they are all the same day OR you can =A3+(A3<TIME(7,0,0)) for all time values which will add 1 day for all values <7am BUT in your example you have end times at 7:30 SO maybe you use =A3+(A3<TIME(7,0,0)) for START times and =A3+(A3<TIME(9,0,0)) for END times assuming you never have any shift <2 hours.
BTW and maybe most important, what version of Excel are you using? Are you on Excel 365? If so we can make 1 or 2 Lambda functions to make this much easier.

[#5]

@m_tarler 

Sample Schedule.xlsx

I am, indeed, using excel 365. 

[#6]

unfortunately it is asking for a WVU sign-in. maybe try setting it for open access or using google docs

[#7]

@m_tarler 

I downloaded a copy. Let me know if this works.

[#8]

@Jen_Sova It worked.  I created a simple LAMBDA function called Toffset that will +1 any time < 7am or whatever time you pick.  I then used a formula 

SUMPRODUCT( (Toffset(start_range) <= Toffset(Start_time) ) * (Toffset(end_range) >= Toffset(End_time) ) )

I used $ to  'lock' columns/rows as needed so I could easily copy down and across

I also added 9/24 to the Toffset for the END times so any end time like 7:30 was also interpreted as the next day also (i.e. up to 9am)

I added a FILTER at the bottom just to 'copy' the values above so I could more easily do a manual check

I started to highlight the correct outputs green and wrong red but after 2 days stopped as it looks like my formula is working.

Let me know if you have more questions.

BTW I changed to SUMPRODUCTS because that will handle an array while COUNTIFS requires a cell range.

[#9]

@m_tarler 

Thank you!!!! You've been an incredible help and I truly appreciate it!