Can time frames/windows be used as a condition for any functions?

[dallas2699]

Hello,
I'm trying to develop a sheet that checks how many employees are active on an hourly basis. The shift patterns are in the A column of the screenshot in a 'start time' - 'finish time' format with a count of how many people are on that shift pattern in the adjacent B column. Based on their shift patterns, we can see how many people we would have per hour. For example, between the hours 15:00-16:00, anyone who has a shift time that starts before 15:00 and ends after 16:00 would be active.

What I want to know is can excel check if any of the cells in column A are within the specified time frames of column H? The obvious answer is just using the SUM function for the shifts that would be active per hour, but the shifts vary daily, so this would have to be manually inputted.
In the screenshot attached, I have highlighted what I'm trying to achieve in the literal sense (surprisingly, that formula doesn't work lol!). If anyone has any ideas that'd be great, thank you!

 

[bebo021999]

Can this sheet be rearranged so that the time frame is split into start and finish across two columns?
For example: A3=10:00; B3=20:00; C3=Count
It will help simplify the formula.

 

[dallas2699]

Can this sheet be rearranged so that the time frame is split into start and finish across two columns?
For example: A3=10:00; B3=20:00; C3=Count
It will help simplify the formula.

Yes that would be fine. How would you proceed from there?

 

[StephenCrump]

@dallas2699, welcome to the Forum!

It will help simplify the formula ...

So, for example, you could do something like this:

	A	B	C	D	E	F	G
1
2	17 Oct 10:00	17 Oct 20:00	6		17 Oct 18:00	17 Oct 19:00	16
3	17 Oct 13:00	17 Oct 21:30	10		17 Oct 19:00	17 Oct 20:00	16
4	17 Oct 19:30	18 Oct 6:00	4		17 Oct 20:00	17 Oct 21:00	14
5	17 Oct 21:30	18 Oct 6:00	7		17 Oct 21:00	17 Oct 22:00	4
6					17 Oct 22:00	17 Oct 23:00	11
7					17 Oct 23:00	18 Oct 0:00	11
Sheet1

Cell Formulas
Range	Formula
F2	F2	=E2+1/24
G2:G7	G2	=SUMIFS(C$2:C$5,A$2:A$5,"<="&E2,B$2:B$5,">="&F2)
E3:F7	E3	=E2+1/24

The headcount of 4 highlighted is misleading. Although there are 4 employees there for the whole hour, you have another 10 (total 14) for the first half-hour, and another 7 (total 11) for the second half hour. The easiest way to allow for this is to show more granularity, i.e. show half-hour slots in this case.

 

[dallas2699]

Okay, so 30 minute intervals would be more appropriate, shifts start at either 30 minutes past or on the hour. Could I then combine the 30 minute intervals to get what I originally asked for? Also, could you breakdown exactly what's going on with the G2:G7 formula? New to excel so would like to learn