+ Reply to Thread
Results 1 to 9 of 9

getting value between dates in pivot table...

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Torrance, CA
    MS-Off Ver
    Excel 2013
    Posts
    75

    getting value between dates in pivot table...

    I've got some data from our shop floor program that shows transaction/clocking dates...but i need to find the total dwell time at each operation.

    The data looks like this:

    Machine #1: 12/1/15
    Machine #1: 12/2/15
    Machine #1: 12/8/15
    Machine #2: 12/8/15
    Machine #2: 12/10/15

    Since i'm pulling multiple reports, i'm looking for a faster way of getting this info pulled, as opposed to looking at the first/last date of each operation

    any ideas how i can manipulate this info?

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: getting value between dates in pivot table...

    Not sure what you are trying to do

    Please add an excel file with your expected result for our better understanding


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    09-27-2012
    Location
    Torrance, CA
    MS-Off Ver
    Excel 2013
    Posts
    75

    Re: getting value between dates in pivot table...

    Here's some sample data...

    Basically, we've got multiple clocking punches at each operation due to different shifts and the days it takes to complete each task. What I'm trying to do is figure out the total time at each operation so I can see where the 'bottlenecks' are...unfortunately, our system only allows me to run 1 work order summary at a time, and the only way I know how to do this is a manual insertion of a formula or modifying the spreadsheet to include a 'start date' and 'end date' column for a pivot table...either way I've got to do some manual work with each report I pull
    Attached Files Attached Files

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: getting value between dates in pivot table...

    Attached file consist 4 pivot table reports based on your given data.

    All the pivot tables targets the first sheet and showing the different layout reports in 5 to 6 mouse clicks.

    Refer the below links to know more about how to use pivot table.

    http://www.contextures.com/CreatePivotTable.html

    http://www.contextures.com/pivottableindex.html
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-27-2012
    Location
    Torrance, CA
    MS-Off Ver
    Excel 2013
    Posts
    75

    Re: getting value between dates in pivot table...

    I appreciate the breakdown...but is there a way to showcase the time it takes in days from the raw data? The dwell time at each operation is what I need to be able to pull. I know I can do that manually, but I was wondering if it was possible v

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: getting value between dates in pivot table...

    Can you please show your expected result layout in excel so that I can understand your requirement in a better way and arrive it in Pivot Report

  7. #7
    Registered User
    Join Date
    09-27-2012
    Location
    Torrance, CA
    MS-Off Ver
    Excel 2013
    Posts
    75

    Re: getting value between dates in pivot table...

    Here you go...sample is to the left of the original data.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,661

    Re: getting value between dates in pivot table...

    Try is array formula
    =MAX(IF($B$2:$B$14=I2,$C$2:$C$14,""))-MIN(IF($B$2:$B$14=I2,$C$2:$C$14,""))
    Attached Files Attached Files
    Last edited by popipipo; 11-20-2015 at 01:31 PM. Reason: attachment
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  9. #9
    Registered User
    Join Date
    09-27-2012
    Location
    Torrance, CA
    MS-Off Ver
    Excel 2013
    Posts
    75

    Re: getting value between dates in pivot table...

    Awesome! That's exactly what I need!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 11-20-2014, 04:09 PM
  2. Select Multiple Dates In Report Filter Pivot Table But Dates Are Not Static
    By biasedobserver in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2014, 03:38 PM
  3. Replies: 0
    Last Post: 05-18-2013, 03:29 AM
  4. Calculate orders past agreed dates in pivot table and averages in pivot tables
    By applesandpears in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-18-2012, 05:26 PM
  5. Pivot Group Dates - how to remove start and end dates from table
    By markoloughlin in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-21-2012, 04:19 AM
  6. Pivot table dates
    By Ozwilly in forum Excel General
    Replies: 3
    Last Post: 05-24-2012, 08:24 PM
  7. Pivot table dates
    By JEastwood in forum Excel General
    Replies: 0
    Last Post: 02-09-2005, 03:02 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1