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

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

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

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