0

I have the following fields in my Excel 2007 pivot table field list.

SaleDate
Amount

I'd like to add a field that is calculated from SaleDate using a formula =WEEKDAY(SaleDate) (or something that can transform the date in that column to a weekday). The result will be.

SaleDate
Amount
SaleDay

After I add SaleDay to a Row Labels and Amount to Values, the result might look like this.

   SaleDay  | Amount
------------+-------
1           |  15646
2           | 851562
3           | 515155
4           | 613215
5           | 516846  
6           | 511662
7           |  25165
------------+-------
Grand Total |3049251
| improve this question | | | | |
0

I don't think you can do this by using Calculated Values in the pivot table itself - they seem to work only on the totals, not the values of individual records.

The easiest thing would be just to use the WEEKDAY() function to add an extra column into your data, then include it in the table.

Assuming the SaleDate information is in column A of your spreadsheet, the Amounts in column B and column headings are in row 1, you could enter SaleDay in cell C1, =WEEKDAY(A2) in cell C2 and copy the formula down the column. The column heading is important to make the pivot table work. Once the source data range for the pivot table is extended to include column C, SaleDay should then be available as a field in your pivot table.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.