Count #of Employees Worked Between Time Periods

[MrRajKumar]

Hello,

I have the following data.

A2;A100 Names
B2:B100 Start Time
C2:C100 End Time

Times are in HH:MM format. In F2:F25 I have hour interval (00-23).

In G2:G25, I would like to get a count of employees who will be working on each time intervals.

eg: if an employee starts at 07:00 AM and end at 12:00 PM, then he will be available in hour interval 7,8,9,10,11,12
Another employee starts at 08:00 and ends at 14:00, then the count for hour 8, should be 2
etc..

Name	Start Time	End Time
A	07:00 AM	11:00 AM
B	08:00 AM	04:00 PM
C	07:00 AM	10:00 AM
D	02:00 PM	09:00 PM
E	03:00 PM	10:00 PM
F	11:00 AM	07:00 PM

 

[awoohaw]

See if Column G here works for you:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Start Time	End Time				Total	A	B	C	D	E	F
2	A	07:00	11:00			00:00	0	0	0	0	0	0	0
3	B	08:00	16:00			01:00	0	0	0	0	0	0	0
4	C	07:00	10:00			02:00	0	0	0	0	0	0	0
5	D	14:00	21:00			03:00	0	0	0	0	0	0	0
6	E	15:00	22:00			04:00	0	0	0	0	0	0	0
7	F	11:00	19:00			05:00	0	0	0	0	0	0	0
8						06:00	0	0	0	0	0	0	0
9						07:00	2	1	0	1	0	0	0
10						08:00	3	1	1	1	0	0	0
11						09:00	3	1	1	1	0	0	0
12						10:00	3	1	1	1	0	0	0
13						11:00	3	1	1	0	0	0	1
14						12:00	2	0	1	0	0	0	1
15						13:00	2	0	1	0	0	0	1
16						14:00	3	0	1	0	1	0	1
17						15:00	4	0	1	0	1	1	1
18						16:00	4	0	1	0	1	1	1
19						17:00	3	0	0	0	1	1	1
20						18:00	3	0	0	0	1	1	1
21						19:00	3	0	0	0	1	1	1
22						20:00	2	0	0	0	1	1	0
23						21:00	2	0	0	0	1	1	0
24						22:00	1	0	0	0	0	1	0
25						23:00	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
H1:M1	H1	=TRANSPOSE($A$2:$A$7)
F2:F25	F2	=TIME(SEQUENCE(24,1,0,1),0,0)
G2	G2	=SUM((($F2>=$B$2:$B$7)*($F2<=$C$2:$C$7)))
H2:M25	H2	=SUM((($F2>=$B$2:$B$7)*($F2<=$C$2:$C$7)*(H$1=$A$2:$A$7)))
G3:G25	G3	=SUM(((F3>=$B$2:$B$7)*(F3<=$C$2:$C$7)))
Dynamic array formulas.

 

[Eric W]

If you're just interested in the totals, try this spill formula:

Book1
	A	B	C	D	E	F	G
1	Name	Start Time	End Time				Total
2	A	7:00	11:00			0:00	0
3	B	8:00	16:00			1:00	0
4	C	7:00	10:00			2:00	0
5	D	14:00	21:00			3:00	0
6	E	15:00	22:00			4:00	0
7	F	11:00	19:00			5:00	0
8						6:00	0
9						7:00	2
10						8:00	3
11						9:00	3
12						10:00	2
13						11:00	2
14						12:00	2
15						13:00	2
16						14:00	3
17						15:00	4
18						16:00	3
19						17:00	3
20						18:00	3
21						19:00	2
22						20:00	2
23						21:00	1
24						22:00	0
25						23:00	0
Sheet6

Cell Formulas
Range	Formula
F2:F25	F2	=TIME(SEQUENCE(24,1,0,1),0,0)
G2:G25	G2	=MMULT((F2:F25>=TRANSPOSE(B2:B7))*(F2:F25<TRANSPOSE(C2:C7)),SEQUENCE(COUNTA(A2:A10),,,0))
Dynamic array formulas.

Note that the totals don't match the totals from awoohaw's formulas. For example, the 10:00 - 11:00 range. I don't think C should be counted. But take a look and let us know.

 

[MrRajKumar]

Sorry friends for the late reply,

How can I tweak this formula for the time that pass midnight. Eg: Start time: 21:00 & End 05:00.
How can I count them the hours during 21 to 05?