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
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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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