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" wrote in message
...
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" wrote in message
...
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 Debs

Forgot to mention that I had already formatted my DAY column in the data set
as Custom=ddd.
Same result, not sure whether TEXT evlauates quicker than WEEKDAY, but it
does save having to remeber to format the source column.

--
Regards
Roger Govier
"Roger Govier" wrote in message news:...
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" wrote in message
...
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

Well, it's a lucky coincidence that 1/1/1900 is a Sunday. <g

Roger Govier wrote:
Hi Debs

Forgot to mention that I had already formatted my DAY column in the data set
as Custom=ddd.
Same result, not sure whether TEXT evlauates quicker than WEEKDAY, but it
does save having to remeber to format the source column.



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