Seniority based on hire date and age

[hajiali]

Im trying to get excel to rank the employees in seniority based on hire date and if hire date then rake them based on the oldest

NAME	HIRE DATE	DOB
BILLY	9/27/1984	10/9/1960
TONY	10/3/1987	7/1/1962
WILLIAM	8/5/1989	7/6/1967
TIM	3/14/1990	9/20/1968
JACK	1/10/1993	3/20/1968
RANDY	1/10/1993	5/28/1968
BRANDON	1/10/1993	8/23/1975

<colgroup><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

in column D

=RANK(b2,$b$2:$b$8)

which does fine until i get to JACK, RANDY, and Brandon and all are raked number 5. I need a tie breaker based on the oldest. of the 3

any suggestion on what to add to Column D formula to check the DOB if employee hired on the same day or any other way to go about doing this

Thanks

 

[Marcelo Branco]

What are the expected/desired results?

M.

 

[hajiali]

employees based on hire date so when using the =RANK(b2,$b$2:$b$8) i get

BILLY	9/27/1984	10/9/1960	1
TONY	10/3/1987	7/1/1962	2
WILLIAM	8/5/1989	7/6/1967	3
TIM	3/14/1990	9/20/1968	4
JACK	1/10/1993	3/20/1968	5
RANDY	1/10/1993	5/28/1968	5
BRANDON	1/10/1993	8/23/1975	5

<tbody>
</tbody>

Desired results should be as follows

BILLY	9/27/1984	10/9/1960	1
TONY	10/3/1987	7/1/1962	2
WILLIAM	8/5/1989	7/6/1967	3
TIM	3/14/1990	9/20/1968	4
JACK	1/10/1993	3/20/1968	5
RANDY	1/10/1993	5/28/1968	6
BRANDON	1/10/1993	8/23/1975	7

<tbody>
</tbody>

because Jack is Older than Randy and Brandon he will be 5th place in rank and Randy is older than Brandon he will follow jack in the ranking. So Ranking Based on Hire Date but if employees are hired on the same day than to rank them based on age.

hope that clarifies

 

[Marcelo Branco]

Try

D2 copied down
=RANK(B2,$B$2:$B$8,1)+COUNTIFS(B$2:B$8,B2,C$2:C$8,"<"&C2)

M.

 

[hajiali]

Thanks Marcelo, were getting close to the resolution: here were the results:

BILLY	9/27/1984	10/9/1960	1
TONY	10/3/1987	7/1/1962	2
WILLIAM	8/5/1989	7/6/1967	3
TIM	3/14/1990	9/20/1968	4
JACK	1/10/1993	3/20/1968	5
RANDY	1/10/1993	5/28/1968	6
BRANDON	1/10/1993	8/23/1975	7
JOHN	3/27/1993	12/4/1976	8
KEVIN	4/2/1993	10/24/1976	9
DARRYL	5/3/1993	2/13/1963	10
REGINALD	9/7/1993	6/19/1965	11
ROBERT	10/22/1993	6/12/1974	13
DAVID	11/24/1993	1/27/1962	14
DARRYLL	1/1/1994	12/4/1968	15
TIM	7/28/1994	1/22/1966	16

<tbody>
</tbody>

Some reason its skipping a number after the first set of same Date of hire it went from 11 to 13. and when going down the list its continues.

 

[hajiali]

here are results further down the list:

JACOB	10/17/2009	12/1/1988	78
OTIS	11/16/2009	8/27/1975	79
LISA	8/19/2010	1/12/1969	80
BASHIR	2/28/2011	8/3/1971	82
MARISSA	3/22/2011	2/22/1986	83
ANTHONY	5/13/2011	2/9/1988	85
TOM	7/20/2011	6/29/1973	86
KEVIN	9/12/2011	7/20/1983	87
NICK	10/4/2011	12/16/1985	88
JUAN	10/10/2011	7/1/1981	89
DAMON	12/21/2011	6/29/1974	90
JACOB	12/25/2011	9/26/1990	91
KATE	2/27/2012	7/22/1981	92

<colgroup><col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"> <col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;"> <col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

as Shown it skipped 81 and 84

 

[Marcelo Branco]

Worked perfectly for me (data sample in post 5)

	A​	B​	C​	D​
1​	NAME​	HIRE DATE​	DOB​	Rank​
2​	BILLY​	9/27/1984​	10/9/1960​	1​
3​	TONY​	10/3/1987​	7/1/1962​	2​
4​	WILLIAM​	8/5/1989​	7/6/1967​	3​
5​	TIM​	3/14/1990​	9/20/1968​	4​
6​	JACK​	1/10/1993​	3/20/1968​	5​
7​	RANDY​	1/10/1993​	5/28/1968​	6​
8​	BRANDON​	1/10/1993​	8/23/1975​	7​
9​	JOHN​	3/27/1993​	12/4/1976​	8​
10​	KEVIN​	4/2/1993​	10/24/1976​	9​
11​	DARRYL​	5/3/1993​	2/13/1963​	10​
12​	REGINALD​	9/7/1993​	6/19/1965​	11​
13​	ROBERT​	10/22/1993​	6/12/1974​	12​
14​	DAVID​	11/24/1993​	1/27/1962​	13​
15​	DARRYLL​	1/1/1994​	12/4/1968​	14​
16​	TIM​	7/28/1994​	1/22/1966​	15​

<tbody>
</tbody>

Formula in D2 copied down
=RANK(B2,$B$2:$B$16,1)+COUNTIFS(B$2:B$16,B2,C$2:C$16,"<"&C2)

M.

 

[hajiali]

It seemed to work when I copied the hire date and DOB on different Columns. Thank you so much. Really appreciated it

 

[Marcelo Branco]

You are welcome. Glad to help.

M.