I'm assuming that you have a date field on each row -- and that date field
contains a real date.

If yes, then I'd add a couple of helper columns so that I could use them to
drill down to what I need.

I'd insert 3 new columns A:C. This will shift all the data over 3 columns.

Add headers to A1:C1:
A1: Day.#
B1: Year
C1: Month

Then with the date in D1, put these 3 formulas in A2:C2:
A2: =TEXT(D2,"ddd.")&INT((DAY(D2)-1)/7)+1
B2: =Year(d2)
C2: =Month(d2)

You may never need the year and month, but some day, you may be happy it's
there.

Then drag these 3 formulas down as far as you need.

Now you can build a pivottable based on your data and these 3 helper columns.

Select your range (A1:e999 or whatever you're using. I had qty in column E.)
Data|pivottable (in xl2003 menus)
Follow the wizard until you get to the step with the Layout button on it.
Click that Layout button
Drag the Year header to the page field
drag the month header to the page field (under the year)
Drag the Day.# header to the row field
Drag the Qty field (or whatever your header is) to the data field.
doubleclick on that "button" in the data field and change it to Average.

And finish up the wizard.

Now you can enjoy the benefits of the pivottable.

You can use the page fields to show just certain years or certain months (or
certain years and months) and see what changes.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


DOUG wrote:

I need to summarize and compare patient demand for appointments going back
over three years. I can pull the data and display the days of the week. In
Ms Excel, is there a way to line up the same days of each month - but not the
dates?

For instance, I need to line up and average all of the first Mondays in May,
all of the first Tuesdays in May and so on.

DOUG in Wichita

--

Dave Peterson