Staff rota table of data to generate a chart

AndrewJelley

New Member
Joined
Nov 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • rota screenshot.PNG
    rota screenshot.PNG
    25.1 KB · Views: 16

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
797
Office Version
  1. 365
Platform
  1. Windows
Hi Andrew,

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

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
797
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1StaffDateSTARTFinishTD StartTD FinishMondaysToday
2Employee129/11/20216:0011:0029/11/2021 6:0029/11/2021 11:001/11/202122/11/2021
3Employee229/11/20219:0013:0029/11/2021 9:0029/11/2021 13:008/11/2021
4Employee329/11/20218:0010:0029/11/2021 8:0029/11/2021 10:0015/11/2021
5Employee429/11/202110:0023:0029/11/2021 10:0029/11/2021 23:0022/11/2021
6Employee529/11/20217:0021:0029/11/2021 7:0029/11/2021 21:0029/11/2021
7Employee130/11/20219:0013:0030/11/2021 9:0030/11/2021 13:006/12/2021
8Employee230/11/20218:0010:0030/11/2021 8:0030/11/2021 10:00
9Employee330/11/202110:0023:0030/11/2021 10:0030/11/2021 23:00
10Employee430/11/20217:0021:0030/11/2021 7:0030/11/2021 21:00
11Employee530/11/20216:0012:0030/11/2021 6:0030/11/2021 12:00
12
1329/11/2021
14STAFF6:007:008:009:0010:0011:0012:0013:00
15Employee1111111  
16Employee2   11111
17Employee3  111   
18Employee4    1111
19Employee5 1111111
20
2130/11/2021
22STAFF6:007:008:009:0010:0011:0012:0013:00
23Employee1   11111
24Employee2  111   
25Employee3    1111
26Employee4 1111111
27Employee51111111 
Sheet1
Cell Formulas
RangeFormula
E2:E11E2=[@Date]+[@START]
F2:F11F2=[@Date]+[@Finish]
I2I2=TODAY()
H2H2=IF(WEEKDAY(EOMONTH(I2,-1)+1)=2,EOMONTH(I2,-1)+1,EOMONTH(I2,-1)+(8 + MOD(2,7))-WEEKDAY(EOMONTH(I2,-1)+1))
H3:H7H3=H2+7
B15:I19B15=SUMPRODUCT((ShiftTBL[Staff]=$A15)*(($A$13+B$14)>=ShiftTBL[TD Start])*(($A$13+B$14)<=ShiftTBL[TD Finish]))
A21A21=A13+1
B23:I27B23=SUMPRODUCT((ShiftTBL[Staff]=$A23)*(($A$21+B$22)>=ShiftTBL[TD Start])*(($A$21+B$22)<=ShiftTBL[TD Finish]))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B15:I27Cell Value=1textNO
Cells with Data Validation
CellAllowCriteria
A13List=$H$2:$H$7
 

AndrewJelley

New Member
Joined
Nov 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,495
Messages
5,832,025
Members
430,104
Latest member
briannnnleong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top