MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table and counting days between dates - help needed


Posted by James K on February 07, 2002 9:56 AM

This is probably a simple thing, but the solution is evading me.

I am querying a database for pivot table data, so I'm working with a defined set of fields. The relevant fields are: UniqueID#, Priority#, StartDate, EndDate.

What I want is: grouped by priority, the average number of days between the start date and the end date.

I can easily do this if I just dump the data into a worksheet, but new records are added constantly, so I'm using a pivot table which pulls data from the database.

I might be stuck because I thought I could use the DAYS360 function as part of a calculated field. That didn't work, and now I can't fugure out how to even approach this. Any pointers about using pivot tables to do a calculation like this would be useful.


Posted by Mark W. on February 07, 2002 10:09 AM

Your query should look something like this...

SELECT Priority AVG(EndDt-StartDt) 'Average'
FROM ...
GROUP By Priority