Basically I have 2 columns each with a list of dates in no particular
order (and containing blank cells too), one planned date column and one
actual date column.

What I need to do is plot this on a graph, and since the number of
dates has no set limit and I dont want to have to plot maybe 100 dates
on the x axis, so i want to group them by week before plotting them,
i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th
jan etc

I have a pivot table that counts how many of each date occurs, i.e. 10
x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.

im sure theres an easyish way of doing it so i can get the 2 lines on
the graph for no. of planned dates each week and no. of actual dates
each week, i just cant see it.

thanks


--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505716

=SUMPRODUCT(--(A2:A200=--"2006-01-04"),--(A2:A200<=--"2006-01-10"))

gives a count for the week in those dates

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"neowok" wrote in
message ...

Basically I have 2 columns each with a list of dates in no particular
order (and containing blank cells too), one planned date column and one
actual date column.

What I need to do is plot this on a graph, and since the number of
dates has no set limit and I dont want to have to plot maybe 100 dates
on the x axis, so i want to group them by week before plotting them,
i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th
jan etc

I have a pivot table that counts how many of each date occurs, i.e. 10
x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.

im sure theres an easyish way of doing it so i can get the 2 lines on
the graph for no. of planned dates each week and no. of actual dates
each week, i just cant see it.

thanks


--
neowok
------------------------------------------------------------------------
neowok's Profile:
http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505716

hmmm that would work if i have a smallish, set number of weeks and i
know what the weeks are, but this is a dynamic document where the date
range is subject to change daily


--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505716

Is the date a row field?

How about rightclicking on it and choosing choose Group and show detail.

Then group by weeks.

If excel's weeks are different than yours, you could use add a new column in the
raw data and determine the week based on whatever rules you follow. Then use
that in the pivottable.



neowok wrote:

Basically I have 2 columns each with a list of dates in no particular
order (and containing blank cells too), one planned date column and one
actual date column.

What I need to do is plot this on a graph, and since the number of
dates has no set limit and I dont want to have to plot maybe 100 dates
on the x axis, so i want to group them by week before plotting them,
i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th
jan etc

I have a pivot table that counts how many of each date occurs, i.e. 10
x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.

im sure theres an easyish way of doing it so i can get the 2 lines on
the graph for no. of planned dates each week and no. of actual dates
each week, i just cant see it.

thanks

--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505716

--

Dave Peterson

the dates are in 2 columns, i went to group but cant see how i could
group them by week (no options for this)

top few cells of one of the planned date column and 2 extra needed for
the graph may look something like this if it all works

Planned week ending numberof items
01-01-06 01-01-06 3
<blank cell 08-01-06 2
<blank cell 15-01-06 1
05-01-06 26-02-06 1
01-01-06
01-01-06
25-02-06
<blank cell
<blank cell
15-01-06
03-01-06
<blank cell
etc etc

the graph could then be based on the week ending and number of items
column for the planned series, and then the same thing again for the
actual series.

I think i need to get an automated split of weeks in one column (B)
(ending on say sundays) and then in column C, the number of dates in
column A which falls during the week in column B

if not that then some other way of getting the same sort of result just
so I can draw the graph.


--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505716

If your field has text or blanks, then you can't use that grouping.

Excel has a =weeknum() function that might be ok right out of the box.

=if(a2="","",weeknum(a2))
???


neowok wrote:

the dates are in 2 columns, i went to group but cant see how i could
group them by week (no options for this)

top few cells of one of the planned date column and 2 extra needed for
the graph may look something like this if it all works

Planned week ending numberof items
01-01-06 01-01-06 3
<blank cell 08-01-06 2
<blank cell 15-01-06 1
05-01-06 26-02-06 1
01-01-06
01-01-06
25-02-06
<blank cell
<blank cell
15-01-06
03-01-06
<blank cell
etc etc

the graph could then be based on the week ending and number of items
column for the planned series, and then the same thing again for the
actual series.

I think i need to get an automated split of weeks in one column (B)
(ending on say sundays) and then in column C, the number of dates in
column A which falls during the week in column B

if not that then some other way of getting the same sort of result just
so I can draw the graph.

--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505716

--

Dave Peterson

hm might be able to use severla more columns and use weeknum. i can use
a macro to copy the weeknum formula down for however many rows the
dates go, then maybe make a pivot table of that column giving me a
count of each weeknum, then somehow convert the weeknums into weeks
that i can use as x axis labels and base the chart off the pivottable
but without using a pivotchart. or something, but thats probly a bit
too complicated to work hehe ill have to fiddle with it on monday


--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505716

Or instead of using the week number, use the date of the first day in the week
(or the Friday of that week or...)

neowok wrote:

hm might be able to use severla more columns and use weeknum. i can use
a macro to copy the weeknum formula down for however many rows the
dates go, then maybe make a pivot table of that column giving me a
count of each weeknum, then somehow convert the weeknums into weeks
that i can use as x axis labels and base the chart off the pivottable
but without using a pivotchart. or something, but thats probly a bit
too complicated to work hehe ill have to fiddle with it on monday

--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505716

--

Dave Peterson