Take Oldest Date based on Name

**lborja** · 03-31-2016, 02:15 PM

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!

**ChemistB** · 03-31-2016, 02:49 PM

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)

**lborja** · 03-31-2016, 03:09 PM

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

**ChemistB** · 03-31-2016, 03:15 PM

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?

**lborja** · 03-31-2016, 03:25 PM

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

**ChemistB** · 03-31-2016, 03:28 PM

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?

**ChemistB** · 03-31-2016, 03:33 PM

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

**lborja** · 03-31-2016, 03:40 PM

yes thank you

**ChemistB** · 03-31-2016, 03:46 PM

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

Take Oldest Date based on Name

LinkBack

Thread Tools

Rate This Thread

Display

Take Oldest Date based on Name

Re: Take Oldest Date based on Name

Re: Take Oldest Date based on Name

Re: Take Oldest Date based on Name

Re: Take Oldest Date based on Name

Re: Take Oldest Date based on Name

Re: Take Oldest Date based on Name

Re: Take Oldest Date based on Name

Re: Take Oldest Date based on Name

Thread Information

Users Browsing this Thread

Similar Threads

find oldest date based on multiple criteria

Formula to find the oldest date based on a given criteria

return oldest date based on call value

Display value based on oldest date and other criteria.

Lookup Adjacent Cell Values based on Newest and Oldest Date

Help: Need to find oldest date based on a location

formula help to get oldest date in column based off another parameter

Bookmarks

Bookmarks

Posting Permissions