Create A Bar Graph from A List of Dates

  • Thread starter Thread starter ghadley_00
  • Start date Start date
G

ghadley_00

Hi,

I have a list of dates spread out over several years and would like to
create a bar graph that shows how many of those dates occurred in each
month. Can anyone recommend a way to do this? I will have to do this a
number of times, so the more automated the process can be, the better.

Thanks,

George

(e-mail address removed)
 
Hi,

You will need to calculate the number of dates in each year.
Assuming your dates are in A2:A26 place years you want to count in
column C. So for example place 2000 in C2, 2001 in C3 and so on till
C9 contains 2007.
In D2 enter the following formula

=SUMPRODUCT(--(YEAR($A$2:$A$26)=C2)*1)

Copy down to D9.
Now create a chart on the range C2:D9

Cheers
Andy
 
I have a list of dates spread out over several years and would like to
create a bar graph that shows how many of those dates occurred in each
month. Can anyone recommend a way to do this? I will have to do this a
number of times, so the more automated the process can be, the better.
Click to expand...

Create a Pivot Chart to count the number of dates that appears in the
list for each month, and plot them as a bar graph. Or create a Pivot
Table and make a custom chart from that.
 
And if I had read the question probably the formula would have been,

=SUMPRODUCT(--(MONTH(A2:A26)=C2)*1)

And the range of summary information would be C2:D13. Changing years in
C2:C13 to Month numbers 1 to 12.

Cheers
Andy
 
And if you used SUMPRODUCT properly you wouldn't use -- and *1 <vbg>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob.

So either,

=SUMPRODUCT((MONTH($A$2:$A$26)=C2)*1)

or,

=SUMPRODUCT(--(MONTH($A$2:$A$26)=C2))

but not both.

Cheers
Andy
 
Back
Top