Countifs is not helping, any suggestions?

[becklog]

Hi,

I need to count the number of employees on a per hour interval. Employee shifts last for 9 hours. So if 2 employees will come in at 4 am and 1 will come in at 7, the result i want to get is like this...
0400 - 2
0500 - 2
0600 - 2
0700 - 3
0800 - 3
.......
1200 - 3
1300 - 1

https://www.dropbox.com/s/llmzf2b0n2cqmg9/Time Interval.xlsx?dl=0

 

[Special-K99]

Your output table isn't long enough.
You've only catered for 24hrs which covers only ONE occurrence of 06:00

Employee 2 works 0400-1300 so he's working at 06:00

Employee 13 starts his shift at 22:00 and works until 07:00 so he's also here at 06:00, but that's 06:00 the NEXT day and your table doesn't cover that time range.


This may be why your COUNTIFS results are inot as expected.

 

[Eric W]

Consider:

Book1
	A	B	C	D	E	F	G	H	I
1	Employee	Start Time	End Time		Interval			Interval
2	Employee 1	1:00:00 PM	10:00:00 PM		1:00 AM	0		1:00 AM	0
3	Employee 2	4:00:00 AM	1:00:00 PM		2:00 AM	0		2:00 AM	0
4	Employee 3				3:00 AM	0		3:00 AM	0
5	Employee 4				4:00 AM	2		4:00 AM	2
6	Employee 5	7:00:00 AM	4:00:00 PM		5:00 AM	2		5:00 AM	2
7	Employee 6				6:00 AM	2		6:00 AM	2
8	Employee 7				7:00 AM	5		7:00 AM	5
9	Employee 8	7:00:00 AM	4:00:00 PM		8:00 AM	5		8:00 AM	5
10	Employee 9	1:00:00 PM	10:00:00 PM		9:00 AM	5		9:00 AM	5
11	Employee 10	10:00:00 AM	7:00:00 PM		10:00 AM	6		10:00 AM	6
12	Employee 11	10:00:00 PM	7:00:00 AM		11:00 AM	6		11:00 AM	6
13	Employee 12				12:00 PM	6		12:00 PM	6
14	Employee 13	10:00:00 PM	7:00:00 AM		1:00 PM	7		1:00 PM	7
15	Employee 14				2:00 PM	7		2:00 PM	7
16	Employee 15				3:00 PM	7		3:00 PM	7
17	Employee 16				4:00 PM	4		4:00 PM	5
18	Employee 17	7:00:00 AM	4:00:00 PM		5:00 PM	4		5:00 PM	5
19	Employee 18	4:00:00 PM	1:00:00 AM		6:00 PM	4		6:00 PM	5
20	Employee 19	4:00:00 AM	1:00:00 PM		7:00 PM	3		7:00 PM	4
21	Employee 20				8:00 PM	3		8:00 PM	4
22	Employee 21				9:00 PM	3		9:00 PM	4
23	Employee 22				10:00 PM	0		10:00 PM	3
24	Employee 23				11:00 PM	0		11:00 PM	3
25	Employee 24	1:00:00 PM	10:00:00 PM		12:00 AM	0		11:59 PM	3
Sheet2

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(B$2:B$25<=E2),--(E2))
I2	{=SUMPRODUCT(--(B$2:B$25<=H2),--(H2B$2:B$25)),--(B$2:B$25<>""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The F2 formula is the rough equivalent of a SUMIFS. But as Special-K99 said, there are definite issues when you have shifts that overlap into the next day. The formula in I2 attempts to handle that as much as possible on a one-day timeframe. (Also note the time change in the H25 cell.) There are still issues though, but that'll probably require some design changes to handle. See if this works well enough for you.

 

[becklog]

Yeah I am fully aware of the shift issue that overlap into the next day, but at least I have now something to begin with. Thanks for help!