Can I Use a Graph to Display A Music Festival Schedule

[sciliberti]

I've been using an Excel worksheet to graphically show the set times and stages for artists at a music festival. A sample can be viewed at https://www.flickr.com/photos/sftekbear/17027927729/.

Each stage is a row and each column represents a 5 minute increment of time. Up until now, I created the schedule by merging the cells between the artist's start and end times, entering their name, adding a thick border around the cell, and use conditional formatting to fill in color based on the cell's row. This works really well, except I have to run through this process for around 160 artists.

Is there a way I can use a chart to achieve the same result? If there is I can't seem to get my head around how to do it.

 

[Steve=True]

Hi sciliberti,

Here is a solution for you that gets you want you want.

Below is how I set up the data with formulas for one additional Band per Stage.

Also notice the settings for your horizontal chart axis in the picture below. It is a stacked bar chart in Excel 2010.

Excel 2010
	A	B	C	D	E	F	G
1		Stage	Start Time	End Time	Duration	Stage Count
2	Band A	4	13:05	13:40	0:35	4.00
3	Band B	5	13:30	15:00	1:30	5.00
4	Band C	4	14:05	14:45	0:40	4.01
5	Band D	1	14:25	15:05	0:40	1.00
6	Band E	2	13:30	14:05	0:35	2.00
7
8
9		Band 1	Start Time	Duration	Gap	Band 2	Duration
10	1	Band D	14:25	0:40	#N/A	#N/A	#N/A
11	2	Band E	13:30	0:35	#N/A	#N/A	#N/A
12	3	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
13	4	Band A	13:05	0:35	1:00	Band C	0:40
14	5	Band B	13:30	1:30	#N/A	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
E2	=D2-C2
E3	=D3-C3
E4	=D4-C4
E5	=D5-C5
E6	=D6-C6
E10	=IF(COUNTIF($B$2:$B$6,$A10)<=1,NA(),INDEX($C$2:$C$6,MATCH($A10,$B$2:$B$6,1),1)-INDEX($C$2:$C$6,MATCH($A10,$B$2:$B$6,0),1))
E11	=IF(COUNTIF($B$2:$B$6,$A11)<=1,NA(),INDEX($C$2:$C$6,MATCH($A11,$B$2:$B$6,1),1)-INDEX($C$2:$C$6,MATCH($A11,$B$2:$B$6,0),1))
E12	=IF(COUNTIF($B$2:$B$6,$A12)<=1,NA(),INDEX($C$2:$C$6,MATCH($A12,$B$2:$B$6,1),1)-INDEX($C$2:$C$6,MATCH($A12,$B$2:$B$6,0),1))
E13	=IF(COUNTIF($B$2:$B$6,$A13)<=1,NA(),INDEX($C$2:$C$6,MATCH($A13,$B$2:$B$6,1),1)-INDEX($C$2:$C$6,MATCH($A13,$B$2:$B$6,0),1))
E14	=IF(COUNTIF($B$2:$B$6,$A14)<=1,NA(),INDEX($C$2:$C$6,MATCH($A14,$B$2:$B$6,1),1)-INDEX($C$2:$C$6,MATCH($A14,$B$2:$B$6,0),1))
F2	=IF(COUNTIF($B$2:$B$6,B2)>1,B2+C2*0.009,B2)
F3	=IF(COUNTIF($B$2:$B$6,B3)>1,B3+C3*0.009,B3)
F4	=IF(COUNTIF($B$2:$B$6,B4)>1,B4+C4*0.009,B4)
F5	=IF(COUNTIF($B$2:$B$6,B5)>1,B5+C5*0.009,B5)
F6	=IF(COUNTIF($B$2:$B$6,B6)>1,B6+C6*0.009,B6)
F10	=IF($E10>0,INDEX($A$2:$A$6,MATCH($A10,$B$2:$B$6,1),1),NA())
F11	=IF($E11>0,INDEX($A$2:$A$6,MATCH($A11,$B$2:$B$6,1),1),NA())
F12	=IF($E12>0,INDEX($A$2:$A$6,MATCH($A12,$B$2:$B$6,1),1),NA())
F13	=IF($E13>0,INDEX($A$2:$A$6,MATCH($A13,$B$2:$B$6,1),1),NA())
F14	=IF($E14>0,INDEX($A$2:$A$6,MATCH($A14,$B$2:$B$6,1),1),NA())
B10	=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,0),1)
B11	=INDEX($A$2:$A$6,MATCH(A11,$B$2:$B$6,0),1)
B12	=INDEX($A$2:$A$6,MATCH(A12,$B$2:$B$6,0),1)
B13	=INDEX($A$2:$A$6,MATCH(A13,$B$2:$B$6,0),1)
B14	=INDEX($A$2:$A$6,MATCH(A14,$B$2:$B$6,0),1)
C10	=INDEX($C$2:$C$6,MATCH(B10,$A$2:$A$6,0),1)
C11	=INDEX($C$2:$C$6,MATCH(B11,$A$2:$A$6,0),1)
C12	=INDEX($C$2:$C$6,MATCH(B12,$A$2:$A$6,0),1)
C13	=INDEX($C$2:$C$6,MATCH(B13,$A$2:$A$6,0),1)
C14	=INDEX($C$2:$C$6,MATCH(B14,$A$2:$A$6,0),1)
D10	=INDEX($E$2:$E$6,MATCH(B10,$A$2:$A$6,0),1)
D11	=INDEX($E$2:$E$6,MATCH(B11,$A$2:$A$6,0),1)
D12	=INDEX($E$2:$E$6,MATCH(B12,$A$2:$A$6,0),1)
D13	=INDEX($E$2:$E$6,MATCH(B13,$A$2:$A$6,0),1)
D14	=INDEX($E$2:$E$6,MATCH(B14,$A$2:$A$6,0),1)
G10	=IF($E10>0,INDEX($E$2:$E$6,MATCH($A10,$B$2:$B$6,1),1),NA())
G11	=IF($E11>0,INDEX($E$2:$E$6,MATCH($A11,$B$2:$B$6,1),1),NA())
G12	=IF($E12>0,INDEX($E$2:$E$6,MATCH($A12,$B$2:$B$6,1),1),NA())
G13	=IF($E13>0,INDEX($E$2:$E$6,MATCH($A13,$B$2:$B$6,1),1),NA())
G14	=IF($E14>0,INDEX($E$2:$E$6,MATCH($A14,$B$2:$B$6,1),1),NA())

Let me know if this helps.

Steve=True