Pivot Table - select status date from list of dates

[AlisaA]

I have a pivot table of project data that contains (amongst other things) type of project, project costing information as well as 3 dates - project start date, project due date, project completion date. What I would like to see in my pivot table is a subtotal of costs per type of project, the sum of the costs to date for each project, and a "status" date that is, essentially :

=IF( Complete >0, Complete, IF ( Due > 0, Due, Start))

I know how to create this as a calculated field in a pivot table, and that works fine. However, because I want to subtotal the costs in the project type, the PT is trying to sum not just the cost columns, but the date column as well. Is there any way to keep the subtotals for the project costs but not subtotal the status dates? Or is there a better way to choose the status date in the PT other than using a calculated field? I am wanting to avoid having to manipulate my project data feed, e.g., embedding this formula in the data source tab.

I appreciate your help!! Thank you!!

 

[Jerry Sullivan]

Hi AlisaA,

I don't think there is a way to do what you describe with a Calculated Field in the Pivot Table.

Calculated Fields can only be used in the Data Field section of the PivotTable and you really want the Status Date to be in one of the Row Label,Column Label or Report Filter areas.

Adding a field to your data source is one work around that you are aware of. I understand your desire not to have to add a field of formulas to the raw data feed to calculate the "Status Date".

Another option is to do a Data Query that adds the Status Date field.
MrExcel MVP Fazza has many threads on this forum that describe how to do this.

This thread explains some of the basics:
http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html

The Status Date field could be calculated and added to the data source using a query like this...

SELECT * , IIF(T.Completion Is Null,IIF(T.Due Is Null,T.Start,T.Due),T.Completion) AS 'Status Date'
FROM MyTable T

In this example MyTable is a static Defined Name that refers to the data source range.