Pivot table displayed by day of the week

I would like to create my pivot table displaying data with the date shown as
day-of-the-week. That is, I have data collected for a period of months. I
want to group my data for several months into Monday, Tuesday, etc. so I see
the results for all of the Mondays, Tuesday, etc. in the several month period.

Example: For the period Jan 1, 2004 to June 30, 2004:

Mon Tue Wed Thu Fri Sat Sun
Data1 35 20 46 58 98 109 232

Any suggestions greatly appreciated; can it be done in Pivot Tables?
Dan

Hi Dan

One way would be to introduce an extra column to your data headed DAY.
In this column enter the formula =WEEKDAY(A2) changing the reference to be
that of the column containing your dates.
Copy the formula down the column to cover the range of your data.
Modify your Pivot Table source range to include the new column - DAY
Make DAY the column value in your PT

--
Regards
Roger Govier
"Dan" <[email protected]> wrote in message
news:[email protected]...
>I would like to create my pivot table displaying data with the date shown
>as
> day-of-the-week. That is, I have data collected for a period of months. I
> want to group my data for several months into Monday, Tuesday, etc. so I
> see
> the results for all of the Mondays, Tuesday, etc. in the several month
> period.
>
> Example: For the period Jan 1, 2004 to June 30, 2004:
>
> Mon Tue Wed Thu Fri Sat
> Sun
> Data1 35 20 46 58 98 109
> 232
>
> Any suggestions greatly appreciated; can it be done in Pivot Tables?
> Dan
>

If you'd like the weekday names, instead of numbers, change Roger's
formula to: =TEXT(A2,"ddd")

Roger Govier wrote:
> Hi Dan
>
> One way would be to introduce an extra column to your data headed DAY.
> In this column enter the formula =WEEKDAY(A2) changing the reference to be
> that of the column containing your dates.
> Copy the formula down the column to cover the range of your data.
> Modify your Pivot Table source range to include the new column - DAY
> Make DAY the column value in your PT
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks, everyone! Just what I wanted...
Dan

"Roger Govier" wrote:

> Hi Dan
>
> One way would be to introduce an extra column to your data headed DAY.
> In this column enter the formula =WEEKDAY(A2) changing the reference to be
> that of the column containing your dates.
> Copy the formula down the column to cover the range of your data.
> Modify your Pivot Table source range to include the new column - DAY
> Make DAY the column value in your PT
>
> --
> Regards
> Roger Govier
> "Dan" <[email protected]> wrote in message
> news:[email protected]...
> >I would like to create my pivot table displaying data with the date shown
> >as
> > day-of-the-week. That is, I have data collected for a period of months. I
> > want to group my data for several months into Monday, Tuesday, etc. so I
> > see
> > the results for all of the Mondays, Tuesday, etc. in the several month
> > period.
> >
> > Example: For the period Jan 1, 2004 to June 30, 2004:
> >
> > Mon Tue Wed Thu Fri Sat
> > Sun
> > Data1 35 20 46 58 98 109
> > 232
> >
> > Any suggestions greatly appreciated; can it be done in Pivot Tables?
> > Dan
> >
>
>
>