grouping using a automated day field in a pivot table
 

I have a excel database that I am using for a pivot table which has an
automated day of the week field in it. I have a formula in Column B for the
day of the week which is worked out by the date in Column A. Formulas I have
tried in Column B have been =if(A10,weekday(A1,2),"") and if(A10,A1,"")
[with B1 being just formatted as a "dddd" date format. Both of these are
showing OK in the column but when used in the pivot table and I try and group
and total on day of the week, then excel is still seeing it as a date (ie
grouping on 15/2/2007,16/2/2007 etc).

Does anyone know how to get a pivot table to group and total on day of the
week, or do I have to enter the data in manually in the database?

Any help much appreciated.
Regards
--
Gai

grouping using a automated day field in a pivot table
 

Formatting doesn't change the underlying value, so the date is still
what's being used in the pivot table. Instead, you could use the TEXT
function to calculate the day:

=IF(A2="","",TEXT(A2,"dddd"))

Gai wrote:
I have a excel database that I am using for a pivot table which has an
automated day of the week field in it. I have a formula in Column B for the
day of the week which is worked out by the date in Column A. Formulas I have
tried in Column B have been =if(A10,weekday(A1,2),"") and if(A10,A1,"")
[with B1 being just formatted as a "dddd" date format. Both of these are
showing OK in the column but when used in the pivot table and I try and group
and total on day of the week, then excel is still seeing it as a date (ie
grouping on 15/2/2007,16/2/2007 etc).

Does anyone know how to get a pivot table to group and total on day of the
week, or do I have to enter the data in manually in the database?

Any help much appreciated.
Regards


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

grouping using a automated day field in a pivot table
 

Thanks!!! That works a treat!
--
Gai


"Debra Dalgleish" wrote:

Formatting doesn't change the underlying value, so the date is still
what's being used in the pivot table. Instead, you could use the TEXT
function to calculate the day:

=IF(A2="","",TEXT(A2,"dddd"))

Gai wrote:
I have a excel database that I am using for a pivot table which has an
automated day of the week field in it. I have a formula in Column B for the
day of the week which is worked out by the date in Column A. Formulas I have
tried in Column B have been =if(A10,weekday(A1,2),"") and if(A10,A1,"")
[with B1 being just formatted as a "dddd" date format. Both of these are
showing OK in the column but when used in the pivot table and I try and group
and total on day of the week, then excel is still seeing it as a date (ie
grouping on 15/2/2007,16/2/2007 etc).

Does anyone know how to get a pivot table to group and total on day of the
week, or do I have to enter the data in manually in the database?

Any help much appreciated.
Regards


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

grouping using a automated day field in a pivot table
 

You're welcome! Thanks for letting me know that it worked.

Gai wrote:
Thanks!!! That works a treat!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html