Staff rota table of data to generate a chart

[AndrewJelley]

Hi,

I have searched the forums but can't quite find what I'm looking for, apologies in advance if I have missed something.

In the attached image is the visual output I desire, where the x axis is the time of operational hours, the y axis is the staff members, and the hours that they are working are displayed as a bar spread across the chart. I have created this manually by just filling in colour in the cells, but what I desire is for this result to be generated from a table of data, so that if I was to make a change to working hours, or add a staff member, it will reflect onto the chart.

This is a rota for a whole week. Our operational days are Monday to Friday and everybody will work the same shift for the entire week, until it changes the week after. So my table of data will have the 'week commencing 29/11/21' 'week commencing 06/12/21' etc. as the column headers, and I would like to generate a chart for each week. Is this possible?

 

[RasGhul]

Hi Andrew,

Your picture represents 1 day, so you will have 5 blocks 1 for each day?

 

[RasGhul]

Maybe you can use this Andrew, Create a excel table called ShiftTBL this will keep your chart calculations dynamic - this could be on a different sheet from the visual output sheet.

A13 has a drop down so you can select Mondays (H2:H7), then the day shift blocks will change, You can then play with the formatting to suit your end requirement

Book1
	A	B	C	D	E	F	G	H	I
1	Staff	Date	START	Finish	TD Start	TD Finish		Mondays	Today
2	Employee1	29/11/2021	6:00	11:00	29/11/2021 6:00	29/11/2021 11:00		1/11/2021	22/11/2021
3	Employee2	29/11/2021	9:00	13:00	29/11/2021 9:00	29/11/2021 13:00		8/11/2021
4	Employee3	29/11/2021	8:00	10:00	29/11/2021 8:00	29/11/2021 10:00		15/11/2021
5	Employee4	29/11/2021	10:00	23:00	29/11/2021 10:00	29/11/2021 23:00		22/11/2021
6	Employee5	29/11/2021	7:00	21:00	29/11/2021 7:00	29/11/2021 21:00		29/11/2021
7	Employee1	30/11/2021	9:00	13:00	30/11/2021 9:00	30/11/2021 13:00		6/12/2021
8	Employee2	30/11/2021	8:00	10:00	30/11/2021 8:00	30/11/2021 10:00
9	Employee3	30/11/2021	10:00	23:00	30/11/2021 10:00	30/11/2021 23:00
10	Employee4	30/11/2021	7:00	21:00	30/11/2021 7:00	30/11/2021 21:00
11	Employee5	30/11/2021	6:00	12:00	30/11/2021 6:00	30/11/2021 12:00
12
13	29/11/2021
14	STAFF	6:00	7:00	8:00	9:00	10:00	11:00	12:00	13:00
15	Employee1	1	1	1	1	1	1
16	Employee2				1	1	1	1	1
17	Employee3			1	1	1
18	Employee4					1	1	1	1
19	Employee5		1	1	1	1	1	1	1
20
21	30/11/2021
22	STAFF	6:00	7:00	8:00	9:00	10:00	11:00	12:00	13:00
23	Employee1				1	1	1	1	1
24	Employee2			1	1	1
25	Employee3					1	1	1	1
26	Employee4		1	1	1	1	1	1	1
27	Employee5	1	1	1	1	1	1	1
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=[@Date]+[@START]
F2:F11	F2	=[@Date]+[@Finish]
I2	I2	=TODAY()
H2	H2	=IF(WEEKDAY(EOMONTH(I2,-1)+1)=2,EOMONTH(I2,-1)+1,EOMONTH(I2,-1)+(8 + MOD(2,7))-WEEKDAY(EOMONTH(I2,-1)+1))
H3:H7	H3	=H2+7
B15:I19	B15	=SUMPRODUCT((ShiftTBL[Staff]=$A15)*(($A$13+B$14)>=ShiftTBL[TD Start])*(($A$13+B$14)<=ShiftTBL[TD Finish]))
A21	A21	=A13+1
B23:I27	B23	=SUMPRODUCT((ShiftTBL[Staff]=$A23)*(($A$21+B$22)>=ShiftTBL[TD Start])*(($A$21+B$22)<=ShiftTBL[TD Finish]))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B15:I27	Cell Value	=1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A13	List	=$H$2:$H$7

 

[AndrewJelley]

Hi Andrew,

Your picture represents 1 day, so you will have 5 blocks 1 for each day?

The shifts will be the same each day for that week, so I don't need to break it down day by day, just a week.