Pivot Table Report: Getting average of difference of two datecolumns

K

Kishori Sharan

Hello

I have data in excel sheet as:

WORK_ID PROJECT START_DT END_DT
1001 P1 1/1/2006 1/10/2006
1002 P1 1/25/2006 2/20/2006
1003 P1 3/15/2006 3/20/2006


I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report always uses sum on calculated field). Other solution is to add a column in my source data to compute the difference of start and end dates and then I can use average. I would like to see this computation by using Pivot table report and the source data only in the above format. Report should look as:

PROJECT AVERAGE TIEM TO FINISH A WORK
P1 13.33

Thanks,
Kishori
(615) 253-4734
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table: summarizing two different ways 0
Average of % in pivot table 1
Average in a Pivot Table 3
Access Report Average Miles/Gallon in Group Footer and Report Footer? 9
Pivot Table Calculations 2
Add more cols to a pivot table 5
pivot table help 0
Slicers and Calculated Items in Pivot Table 2

Top