# Take Oldest Date based on Name

1. ## 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. ## 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)

3. ## 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. ## 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. ## Re: Take Oldest Date based on Name

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

6. ## 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?

7. ## Re: Take Oldest Date based on Name

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

8. ## Re: Take Oldest Date based on Name

yes thank you

9. ## 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.")

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

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