grouping using a automated day field in a pivot table

[Guest]

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(A1>0,weekday(A1,2),"") and if(A1>0,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]

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"))

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(A1>0,weekday(A1,2),"") and if(A1>0,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

 

[Guest]

Thanks!!! That works a treat!
--
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"))

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(A1>0,weekday(A1,2),"") and if(A1>0,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]

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