Graph

[Craig1]

Hi All,
I want to create a bar chart or graph based on how many times a colour appears in column B based on the start and end date of a month. This would be added to on a monthly basis.
Do I start from the 1st Jan to 31 Dec and have zero's for all the months to come.

Excel Workbook
	A	B
1	Date	Department
2	25/02/2011	Blue
3	26/02/2011	Blue
4	27/02/2011	Blue
5	28/02/2011	Blue
6	01/03/2011	Blue
7	02/03/2011	Blue
8	03/03/2011	Blue
9	04/03/2011	Blue
10	05/03/2011	Blue
11	06/03/2011	Blue
12	07/03/2011	Blue
13	08/03/2011	Blue
14	09/03/2011	Blue
15	10/03/2011	Blue
16	11/03/2011	Blue
17	12/03/2011	Blue
18	13/03/2011	Blue
19	14/03/2011	Pink
20	15/03/2011	Pink
21	16/03/2011	Pink
22	17/03/2011	Pink
23	18/03/2011	Pink
24	19/03/2011	Pink
25	20/03/2011	Pink
26	21/03/2011	Pink
27	22/03/2011	Yellow
28	23/03/2011	Yellow
29	24/03/2011	Yellow
30	25/03/2011	Yellow
31	26/03/2011	Yellow
32	27/03/2011	Yellow
33	28/03/2011	Yellow
34	29/03/2011	Pink
35	30/03/2011	Blue
36	31/03/2011	Blue
37	01/04/2011	Blue
38	02/04/2011	Blue
39	03/04/2011	Yellow
40	04/04/2011	Pink
41	05/04/2011	Pink
42	06/04/2011	Pink
43	07/04/2011	Pink
44	08/04/2011	Pink
45	09/04/2011	Pink
46	10/04/2011	Pink
47	11/04/2011	Pink
48	12/04/2011	Pink
49	13/04/2011	Pink
50	14/04/2011	Yellow
51	15/04/2011	Yellow
52	16/04/2011	Yellow
53	17/04/2011	Yellow
54	18/04/2011	Yellow
55	19/04/2011	Yellow
56	20/04/2011	Yellow
57	21/04/2011	Yellow
58	22/04/2011	Yellow
Sheet1

I want to count how many times the 3 colours in colomn B based on the 1st March to 31st March. But I will need this to be able to update as the year goes on as more date is added.

Any help would be appreciated.

 

[Andrew Poulsom]

Create a pivot table and pivot chart from your data and Group the dates by Month and Year. Sample:

<TABLE style="WIDTH: 394pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=522 border=0 x:str><COLGROUP><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" span=3 width=90><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 96pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=128 height=17>Count of Department</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 38pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=50> </TD><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=90>Department</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=90> </TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=90> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 56pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=74> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Years</TD><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Date</TD><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Blue</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Pink</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Yellow</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Grand Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2011</TD><TD class=xl27 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Feb</TD><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl28 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Mar</TD><TD class=xl29 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>15</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>9</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>7</TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>31</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl34 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl28 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Apr</TD><TD class=xl29 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>10</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>10</TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>22</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl31 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Grand Total</TD><TD class=xl35 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl31 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>21</TD><TD class=xl32 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>19</TD><TD class=xl32 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>17</TD><TD class=xl33 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>57</TD></TR></TBODY></TABLE>

 

[Craig1]

Thanks for that Andrew I will give it a try.

Craig