[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
blog measure 1

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
Blog measure 2

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

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.

blog post 4

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

One comment

  1. Thank you very much for this, I have been trawling the internet for weeks for a way to get a report like this. All I need to do now if find a way to stack the different measures on top of each other rather than have them side by side.
    Thank you

Leave a Reply