Pivot table displayed by day of the week

[Guest]

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

 

[Roger Govier]

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]

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

 

[Guest]

Thanks, everyone! Just what I wanted...
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