+ Reply to Thread
Results 1 to 3 of 3

finding oldest date and difference between dates for each unique ID in pivot table

  1. #1
    Registered User
    Join Date
    07-23-2020
    Location
    Davenport IA
    MS-Off Ver
    2017
    Posts
    1

    finding oldest date and difference between dates for each unique ID in pivot table

    What you will find attached is a sample of data from a pivot table. There are several ID numbers (BA1008, BA10010, etc), each of which is associated with 1 or more dates. I would like to accomplish 2 tasks:
    1- include only oldest date for each unique member ID
    2- determine the number of days between the most recent and oldest dates for each unique member ID

    Top (bold) output= ID in rows and date in values. If I attempt to change count to MIN I get a "0" value for each ID- same thing happens if I change count to MAX. The formatting of the date values in the original dataset has been set to "date".

    Bottom (not bold) output= both ID and date in the values section

    Hopefully my query is clear- these things are difficult to describe sometimes
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-23-2020
    Location
    Sacramento, California
    MS-Off Ver
    Office 365
    Posts
    4

    Re: finding oldest date and difference between dates for each unique ID in pivot table

    The sample data is not a pivot table.
    Formulas will be different if it is just a range.
    Could you attach an example with a pivot table used as you described in your question?

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: finding oldest date and difference between dates for each unique ID in pivot table

    Hello niugrads1999 and Welcome to Excel Forum.
    As this is your first post I will attempt to figure out the layout of the source data (columns A:B on Sheet2)
    Two helper columns (C:D) are then added to the source data.
    The first helper column displays the oldest date associated with an ID using: =INDEX(B$2:B$12,MATCH(A2,A$2:A$12,0))+0
    The second helper column displays the range between dates using: =B2-C2+1
    The pivot table utilizes the Max of each of the helper columns referenced per ID.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Finding the oldest unworked date...
    By DukeRollo in forum Excel General
    Replies: 3
    Last Post: 10-16-2019, 09:16 AM
  2. [SOLVED] How to calculate date difference between two dates with its related unique ID.
    By JayeshG in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-27-2018, 12:55 PM
  3. [SOLVED] finding oldest real date in a date range
    By JakeMann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-01-2017, 04:50 AM
  4. [SOLVED] Finding the Oldest Date in a Column using a VBA macro
    By TheScott in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2015, 04:02 AM
  5. [SOLVED] Finding the latest and oldest dates in a filtered column.
    By skyping in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-04-2015, 05:35 PM
  6. Replies: 5
    Last Post: 10-01-2009, 11:48 AM
  7. Calculate Date difference in pivot table
    By shinymcshires in forum Excel General
    Replies: 0
    Last Post: 12-16-2008, 02:46 PM

Tags for this Thread

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