Pivot Chart / Gnatt Chart / Scheduling Slots for student work experience

[Noobs]

Background
I have about 200 students who will be doing work based experience over the course of a 6 month period.
- Each pupils time on work experience will vary depending on what the employer prefers ie 1 week, 2 week, 3 weeks.

Is there a way to turn a pivot table/chart into a "scheduling slot" gnatt chart, indicating easily at a glance when which pupils are away on work experience?

In the Row Labels I have:
-Year Head,
-Teacher,
-Pupil,

In the Column Labels
-WeekNum (calc of start date)

Values
-Count of Pupils


I have another field called duration but I just dont know how to build this into pivot so that I can create a sideways chart highlighting when each pupil will be away from school. Appreciate any help

 

[Steve=True]

Hi Noobs,

It is really hard to do in a bar chart with a pivot table, but you can use your pivot table to create the gantt chart using conditional formatting.
Here is my rendition:

After creating the pivot table, I did the following:
1) Set Field Settings>Subtotals = None for Year, Teacher and Pupil
2) Set Pivot Table Options>Totals & Filters = Unchecked for Grand Totals on Rows and Columns
3) Created Conditional Formatting on the Pivot Table = g3=1 then Fill=Green, Font=Green and Border = Outline

Here is a link to tutorials on gantt charts and conditional formatting:
the-tricks-to-writing-a-conditional-formatting-rule-formula

how-to-make-a-non-chart-excel-dashboard-chart-heat-index

Hope that works for you. Let me know how you make out.

Steve=True

 

[Noobs]

Hi Steve=True, This is phenomenal!!

Thank you for taking a moment to show me how to do this and the links.

Its great and works perfectly, I am already thinking about different ways I can approach the problem - like breaking the data down from weeknum to day - so thanks again!! I do have a further query though, if you don't mind assisting?

Would there be a way to show the below. Maybe via calculated formulas (which I am not very good at with pivots) that show as values with their own conditional formatting? So something like:

Pre-course prepwork = 2 (Orange) ie Teacher2;Pupil1 does precourse homework from weeknum6,7 & 8. etc
Sickness = 3 (Red) ie Teacher2;Pupil1 is sick on weeknum 10
Post-course homework = 4 (Blue) ie Teacher1;Pupil3 does postcourse homework in weeknum9

Thanks very much