Creating Heat Chart for Schedules

[EricGoBills]

Hello,

First post on here but have used this site countless times as a reference.

I am creating a heat chart for staffing purposes to show how many people are on shift for a specific day/time.


The first sheet has schedule info (Name, Start Time, End Time, and 1/0 for each day of the week they work)

---

A

B

C

D

E

F

G

H

I

J

K

L

1

Name

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Start EST

End EST

Duration

Shift

2

John

1

1

1

1

1

0

0

6:00

14:30

8:30

A

3

Jane

1

1

1

1

1

0

0

13:00

22:00

9:00

B

4

Jack

0

0

1

1

1

1

1

22:00

7:00

9:00

C

<tbody>
</tbody>

---

The second sheet is where I am creating my heat chart. Using the following formula it will calculate for everyone except the employees who work the overnight. (end shift < start shift)

(formula from cell B3)

=SUMIFS(Schedule!$B:$B,Schedule!$B:$B,1,Schedule!$I:$I,"<="&B$2,Schedule!$J:$J,">"&B$2)

---

	A	B	C	D	E	F	G	H	I	J	K	L
1
2		12:00:00 AM	12:30:00 AM	1:00:00 AM	1:30:00 AM	2:00:00 AM	2:30:00 AM	3:00:00 AM	3:30:00 AM	4:00:00 AM	4:30:00 AM	5:00:00 AM
3	Sunday	0	0	0	0	0	0	0	0	0	0	0
4	Monday	0	0	0	0	0	0	0	0	0	0	0
5	Tuesday	0	0	0	0	0	0	0	0	0	0	0
6	Wednesday	0	0	0	0	0	0	0	0	0	0	0
7	Thursday	0	0	0	0	0	0	0	0	0	0	0
8	Friday	0	0	0	0	0	0	0	0	0	0	0
9	Saturday	0	0	0	0	0	0	0	0	0	0	0

<tbody>
</tbody>

---

Looking for any suggestions on how to incorporate the overnight shifts into this formula. ("Jack" in the example) I have played with IF statements checking if Start Time > End Time, but can't seem to get it to work.

 

[VBA Geek]

Try this:



Worksheet Formulas

Cell	Formula
K2	=MOD(J2-I2,1)

<thead>
</thead><tbody>
</tbody>

and drag down

Worksheet Formulas

Cell	Formula
B7	=SUMPRODUCT(N(B$2:B$4=1),N($I$2:$I$4<=$A7),N($I$2:$I$4+$K$2:$K$4>=$A7))+SUMPRODUCT(N(H$2:H$4=1),N($I$2:$I$4+$K$2:$K$4>=(1+$A7)))
C7	=SUMPRODUCT(N(C$2:C$4=1),N($I$2:$I$4<=$A7),N($I$2:$I$4+$K$2:$K$4>=$A7))+SUMPRODUCT(N(B$2:B$4=1),N($I$2:$I$4+$K$2:$K$4>=(1+$A7)))
D7	=SUMPRODUCT(N(D$2:D$4=1),N($I$2:$I$4<=$A7),N($I$2:$I$4+$K$2:$K$4>=$A7))+SUMPRODUCT(N(C$2:C$4=1),N($I$2:$I$4+$K$2:$K$4>=(1+$A7)))
E7	=SUMPRODUCT(N(E$2:E$4=1),N($I$2:$I$4<=$A7),N($I$2:$I$4+$K$2:$K$4>=$A7))+SUMPRODUCT(N(D$2:D$4=1),N($I$2:$I$4+$K$2:$K$4>=(1+$A7)))
F7	=SUMPRODUCT(N(F$2:F$4=1),N($I$2:$I$4<=$A7),N($I$2:$I$4+$K$2:$K$4>=$A7))+SUMPRODUCT(N(E$2:E$4=1),N($I$2:$I$4+$K$2:$K$4>=(1+$A7)))
G7	=SUMPRODUCT(N(G$2:G$4=1),N($I$2:$I$4<=$A7),N($I$2:$I$4+$K$2:$K$4>=$A7))+SUMPRODUCT(N(F$2:F$4=1),N($I$2:$I$4+$K$2:$K$4>=(1+$A7)))
H7	=SUMPRODUCT(N(H$2:H$4=1),N($I$2:$I$4<=$A7),N($I$2:$I$4+$K$2:$K$4>=$A7))+SUMPRODUCT(N(G$2:G$4=1),N($I$2:$I$4+$K$2:$K$4>=(1+$A7)))

<thead>
</thead><tbody>
</tbody>

and drag down

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Start EST	End EST	Duration	Shift
2	John	1	1	1	1	1	0	0	6:00	14:30	8:30	A
3	Jane	1	1	1	1	1	0	0	13:00	22:00	9:00	B
4	Jack	0	0	1	1	1	1	1	22:00	7:00	9:00	C
5
6		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
7	00:00	1	0	0	1	1	1	1
8	00:30	1	0	0	1	1	1	1
9	01:00	1	0	0	1	1	1	1
10	01:30	1	0	0	1	1	1	1
11	02:00	1	0	0	1	1	1	1
12	02:30	1	0	0	1	1	1	1
13	03:00	1	0	0	1	1	1	1
14	03:30	1	0	0	1	1	1	1
15	04:00	1	0	0	1	1	1	1
16	04:30	1	0	0	1	1	1	1
17	05:00	1	0	0	1	1	1	1
18	05:30	1	0	0	1	1	1	1
19	06:00	2	1	1	2	2	1	1
20	06:30	2	1	1	2	2	1	1
21	07:00	2	1	1	2	2	1	1
22	07:30	1	1	1	1	1	0	0
23	08:00	1	1	1	1	1	0	0
24	08:30	1	1	1	1	1	0	0
25	09:00	1	1	1	1	1	0	0
26	09:30	1	1	1	1	1	0	0
27	10:00	1	1	1	1	1	0	0
28	10:30	1	1	1	1	1	0	0
29	11:00	1	1	1	1	1	0	0
30	11:30	1	1	1	1	1	0	0
31	12:00	1	1	1	1	1	0	0
32	12:30	1	1	1	1	1	0	0
33	13:00	2	2	2	2	2	0	0
34	13:30	2	2	2	2	2	0	0
35	14:00	2	2	2	2	2	0	0
36	14:30	2	2	2	2	2	0	0
37	15:00	1	1	1	1	1	0	0
38	15:30	1	1	1	1	1	0	0
39	16:00	1	1	1	1	1	0	0
40	16:30	1	1	1	1	1	0	0
41	17:00	1	1	1	1	1	0	0
42	17:30	1	1	1	1	1	0	0
43	18:00	1	1	1	1	1	0	0
44	18:30	1	1	1	1	1	0	0
45	19:00	1	1	1	1	1	0	0
46	19:30	1	1	1	1	1	0	0
47	20:00	1	1	1	1	1	0	0
48	20:30	1	1	1	1	1	0	0
49	21:00	1	1	1	1	1	0	0
50	21:30	1	1	1	1	1	0	0
51	22:00	1	1	2	2	2	1	1
52	22:30	0	0	1	1	1	1	1
53	23:00	0	0	1	1	1	1	1
54	23:30	0	0	1	1	1	1	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​