[Trick] Using date fields as values in a PivotTable

Often I get the request to build a PivotTable in Excel, that can easily show important/key dates from projects, tasks and milestones. Many end up in a dead end when trying to get the dates to be shown in a useful way.

The thing that seems to go wrong every time, is not knowing about how to get dates into the “values” section of an Excel PivotTable. Doing it wrong will either have your date fields and up as “counts” in the value section or as a separate column line.

The solution is actually rather simple. Follow the below 3 steps, and you should be on your way to finalize that report with key date information.

In summary we need to create our own date field by using what is called “measures”. To follow the steps, be sure you are already in your Excel spreadsheet connected to a table that contains date information such as the Project Online Odata feed “Tasks”.

Step 1 – Select “New Measure” using PowerPivot

Step 2 – Create a simple formula using =MAX([Tasks.TasksFinishDate]) or any other date field you want converted. The MAX is telling to take the maximum value in case you had multiple dates for each field

Step 3 – Insert your new “measure” in the Values section and group by whatever custom field you want to

Bonus trick
The above scenario can actually allow you to report on specific phases, tasks and milestones without any roll-up to the Project Summary level. This means no need to create 15 custom fields rolled up in order to show 15 different dates at the project level.

From an end user perspective this means “freedom” to manually choose which elements of the plan correspond to key dates in your organizations project model or WBS structure. Too often I see people setting up the most complex plans with the use of multiple custom fields, where they could have simply used one.

In the below screenshot a user simply have to choose a “phase” from a pre-defined lookup. Alternatively, you create projects based on a template, and the key dates could be tagged in advance.

Good luck simplifying your use of Microsoft Project Online or Project Server.