Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Create A Bar Graph from A List of Dates
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Create A Bar Graph from A List of Dates
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 wrote: 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Create A Bar Graph from A List of Dates
On Sat, 8 Dec 2007, in microsoft.public.excel.charting,
" said: 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. 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Create A Bar Graph from A List of Dates
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 Andy Pope wrote: 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 wrote: 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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Create A Bar Graph from A List of Dates
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) "Andy Pope" wrote in message ... 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 Andy Pope wrote: 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 wrote: 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 |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Create A Bar Graph from A List of Dates
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 Bob Phillips wrote: And if you used SUMPRODUCT properly you wouldn't use -- and *1 <vbg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
How to create a list of unique dates? | Excel Discussion (Misc queries) | |||
How to create adress list so can mail merge and create labels? | Excel Discussion (Misc queries) | |||
create a filled in calendar from list of dates and notes | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions |