Consulting

Results 1 to 3 of 3

Thread: Dates in Pivot Table

  1. #1

    Dates in Pivot Table

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •