[VBA] Average number of days between dates for multiple entries in PivotCache

[Cornerback]

Hi everybody,

my current task is to evaluate data from a MSSQL-DB. This is done by creating a pivot cache, from which pivot charts are generated. This works fine

Every DB-entry has a field with a start date and a field with a end-date. My task is to get the number of days between those two dates for EVERY entry in the DB and then calculate the average of those days. (the entries are selected by entering the start- and end-date in a userform, if this does matter...)

Any ideas how to do this in VBA?

kind regards

Cornerback

 

[iyyi]

If you have the Start Date in Column A and the End Date in column B, then you can use the next VBA statement:
avg = [SUM(B2:B10000-A2:A10000)/COUNT(A2:A10000)]

 

[Cornerback]

Okay, doing it with a temporary table sound reasonable. Thanks

is there, however, any way to calculate those values directly from the PivotCache or from the MSSQL-Table without writing them into a excel-sheet first?