Hours Calculations

[vmjan02]

I have a shift roster like

Shift Flag​	A	B	C	D	E
Shift Start Time​	8:00:00	10:00:00	12:00:00	16:00:00	0:00:00
Shift End Time​	16:00:00	18:00:00	20:00:00	0:00:00	8:00:00

and the roster format as

Date	Day	Week Day	Emp 1	Emp 2	Emp 3	Emp 4	Emp 5	Emp 6
19-Nov	Sunday	WK-DAY	A	D	C	B	D	E
20-Nov	Sunday	WK-DAY	WO	D	C	B	D	E
21-Nov	Sunday	WK-DAY	WO	D	C	B	D	E
22-Nov	Sunday	WK-DAY	E	WO	WO	A	D	WO

with respect to date how can we get the actual emp counts hourly. hourly will be 0 to 23.
for eg. 8am count will be 1 shift A
10am count will be 2 shift (A&B)
I am not able to fig out. any help

Result

Hours	Agent Count
0	Count of agent here
1
2
3
4
5
6
7
8
9
10
11

 

[Shiseiji]

See if the information provided by the late Chip Pearson can help. It appears his site has also passed from the public domain, but is still available in the Archive: Dates And Times In Excel

Ron

 

[jasonb75]

The way you want your results is going to be formula unfriendly. See if this helps. Note that the 'Shift End Time' for Shift D needs to be 24:00:00, not 00:00:00 (although it is still formatted to appear as 00:00:00)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Shift Flag	A	B	C	D	E
2	Shift Start Time	8:00:00	10:00:00	12:00:00	16:00:00	0:00:00
3	Shift End Time	16:00:00	18:00:00	20:00:00	0:00:00	8:00:00
4
5	Date	Day	Week Day	Emp 1	Emp 2	Emp 3	Emp 4	Emp 5	Emp 6
6	19/11/2019	Sunday	WK-DAY	A	D	C	B	D	E
7	20/11/2019	Sunday	WK-DAY	WO	D	C	B	D	E
8	21/11/2019	Sunday	WK-DAY	WO	D	C	B	D	E
9	22/11/2019	Sunday	WK-DAY	E	WO	WO	A	D	WO
10
11		0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
12	19/11/2019	1	1	1	1	1	1	1	1	1	1	2	2	3	3	3	3	4	4	3	3	2	2	2	2
13	20/11/2019	1	1	1	1	1	1	1	1	0	0	1	1	2	2	2	2	4	4	3	3	2	2	2	2
14	21/11/2019	1	1	1	1	1	1	1	1	0	0	1	1	2	2	2	2	4	4	3	3	2	2	2	2
15	22/11/2019	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Sheet11

Cell Formulas
Range	Formula
B12:Y15	B12	=SUMPRODUCT(($B$2:$F$2<=(B$11/24))*($B$3:$F$3>(B$11/24))*COUNTIF($D6:$I6,$B$1:$F$1))

 

[vmjan02]

Cool, its worked.

 

[MARK858]

See if the information provided by the late Chip Pearson can help. It appears his site has also passed from the public domain, but is still available in the Archive

Just for info it is back up now ?

 

[Shiseiji]

And I should have preceded with "Greatly missed." Am really glad people stood up to work with his family to keep the site up.