+ Reply to Thread
Results 1 to 9 of 9

Take Oldest Date based on Name

  1. #1
    Registered User
    Join Date
    12-09-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    MS Office 2010
    Posts
    19

    Take Oldest Date based on Name

    Hello,

    I am trying to create this list for work where I can see how long a file has been with a person.

    Here is the setup

    Column A (List of names) Column B

    Smith, John 3/2016
    Smith, John 2/2016
    Smith, John 01/2015
    Doe, Jane 4/2015
    Doe, Jane 9/2014


    What I want as a result in Column C: the duration

    Smith, John would yield in column " 1 yr, 2mo" since he had the file from 01/2015 until 3/2016
    Doe, Jane would yield in column C "7mo" since he had the file from 9/2014 to 4/2015

    any ideas would be much appreciated!

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: Take Oldest Date based on Name

    Questions:
    1. Are names always grouped together or are they randomly mixed? Is the top instance of the name always the most recent and the bottom instance the oldest?
    2. Are your dates really dates or are they text? For example, if the date is in B4, in a blank cell, if you put =ISNUMBER(B4) does it come back true or false? (Dates are numbers)
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    12-09-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    MS Office 2010
    Posts
    19

    Re: Take Oldest Date based on Name

    1. The top is the most recent and bottom is oldest. To be more specific, a file would start from person A and he can have it for 6 months (for example) and then it gets passed on to person B for another 9 months (for example). After person B, it might go back to person A for another 3 months. So the name can re-appear on the list.

    2. That formula when I entered it gives me TRUE, so

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: Take Oldest Date based on Name

    Okay, so when person A has the file twice, do you want that total time or do you want two separate times for Person A?

  5. #5
    Registered User
    Join Date
    12-09-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    MS Office 2010
    Posts
    19

    Re: Take Oldest Date based on Name

    separate times. the list really goes by according to the date timeline

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: Take Oldest Date based on Name

    In Column C, I put an "a" in C1, then in C2 copied down

    =IF(A2=A3,"",DATEDIF(B2,INDEX($B$1:B2,MATCH("zzz",$C$1:$C1)),"Y")&" yr "&DATEDIF(B2,INDEX($B$1:B2,MATCH("zzz",$C$1:$C1)),"YM")&" mo.")
    See attachment. Would that work for you?
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: Take Oldest Date based on Name

    Nevermind, it doesn't work properly. Back to the drawing board.

  8. #8
    Registered User
    Join Date
    12-09-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    MS Office 2010
    Posts
    19

    Re: Take Oldest Date based on Name

    yes thank you

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: Take Oldest Date based on Name

    Okay, moved everything down 1 row
    In C2 (under "Results")

    =IF(A2=A3,"",DATEDIF(B2,INDEX($B$1:B1,LOOKUP(2,1/(LEN($C1:$C$1)>0),ROW($A$1:$A1))+1),"Y")&" yr "&DATEDIF(B2,INDEX($B$1:B1,LOOKUP(2,1/(LEN($C1:$C$1)>0),ROW($A$1:$A1))+1),"YM")&" mo.")
    Attached Files Attached Files

+ 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. find oldest date based on multiple criteria
    By Bax in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-29-2015, 09:01 AM
  2. Replies: 2
    Last Post: 05-01-2014, 04:39 PM
  3. return oldest date based on call value
    By 288enzo in forum Excel General
    Replies: 8
    Last Post: 02-28-2014, 10:50 PM
  4. Display value based on oldest date and other criteria.
    By Permagrin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2009, 09:48 AM
  5. Lookup Adjacent Cell Values based on Newest and Oldest Date
    By nevi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2008, 02:27 PM
  6. Help: Need to find oldest date based on a location
    By paindivine in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-11-2006, 03:14 PM
  7. formula help to get oldest date in column based off another parameter
    By slimswol in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2006, 10:52 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