PDA

View Full Version : Dates in Pivot Table



kathyb0527
11-17-2008, 05:07 PM
I am trying to calculate metrics from rows of data. At first, I tried to use macros and formulas (thanks XLD for suggesting array formulas). But now my manager wants the data combined for each project (which can have several lines). I've tried pivot tables, but I just can't seem to get anything to calculate in regards to the dates. I've attached a spreadsheet with example data. I'm not even sure this can be done since the data is not very consistent.

Thanks for any insight!
Kathyb0527

Bob Phillips
11-18-2008, 03:08 AM
Kathy,

I think that is too complex for us to be able to glean what you want, what should happen. One sheet has UDFs in your Personal.xls, so we just get #NAME.

Can you take a few steps back and give us a synopsis of the data, the objectives et al?

kathyb0527
11-18-2008, 10:01 AM
I'll try to explain, but I have concerns about how the spreadsheet is organized (holes in the data for starters).

For each project (Column D) we get several shipments to process in the lab. There is one row for each shipment received (there are 200+ rows). After all the shipments have been processed, a report is issued. I've worked out the average number of days per shipment between the different steps, but now I need to see if there is a way to get the average number of days between each step per project. For instance:

Project 05-048 has 7 shipments (Rows 5-11) I need the average number of days between Sample Receipt and Lab start (average of P5:S11), Lab start to Lab End (average T5:T11) etc.

I've attached another spreadsheet with the UDF updated and the row titles more pivot table friendly.

Hope this is clearer.