Seniority based on hire date and age

H

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
626
Office Version
  1. 2016
Platform
  1. Windows
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
[TABLE="width: 681"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]

NAME[/TD]
[TD]

HIRE DATE[/TD]
[TD]

DOB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BILLY[/TD]
[TD]9/27/1984[/TD]
[TD]10/9/1960[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TONY[/TD]
[TD]10/3/1987[/TD]
[TD]7/1/1962[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WILLIAM[/TD]
[TD]8/5/1989[/TD]
[TD]7/6/1967[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TIM[/TD]
[TD]3/14/1990[/TD]
[TD]9/20/1968[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JACK[/TD]
[TD]1/10/1993[/TD]
[TD]3/20/1968[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RANDY[/TD]
[TD]1/10/1993[/TD]
[TD]5/28/1968[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BRANDON[/TD]
[TD]1/10/1993[/TD]
[TD]8/23/1975[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

in column D
Code: 
=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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
employees based on hire date so when using the =RANK(b2,$b$2:$b$8) i get

[TABLE="class: cms_table, width: 681"]
<tbody>[TR]
[TD]BILLY[/TD]
[TD]9/27/1984[/TD]
[TD]10/9/1960[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TONY[/TD]
[TD]10/3/1987[/TD]
[TD]7/1/1962[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]WILLIAM[/TD]
[TD]8/5/1989[/TD]
[TD]7/6/1967[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]TIM[/TD]
[TD]3/14/1990[/TD]
[TD]9/20/1968[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]JACK[/TD]
[TD]1/10/1993[/TD]
[TD]3/20/1968[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]RANDY[/TD]
[TD]1/10/1993[/TD]
[TD]5/28/1968[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BRANDON[/TD]
[TD]1/10/1993[/TD]
[TD]8/23/1975[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Desired results should be as follows

[TABLE="class: cms_table, width: 681"]
<tbody>[TR]
[TD]BILLY[/TD]
[TD]9/27/1984[/TD]
[TD]10/9/1960[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TONY[/TD]
[TD]10/3/1987[/TD]
[TD]7/1/1962[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]WILLIAM[/TD]
[TD]8/5/1989[/TD]
[TD]7/6/1967[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]TIM[/TD]
[TD]3/14/1990[/TD]
[TD]9/20/1968[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]JACK[/TD]
[TD]1/10/1993[/TD]
[TD]3/20/1968[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]RANDY[/TD]
[TD]1/10/1993[/TD]
[TD]5/28/1968[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]BRANDON[/TD]
[TD]1/10/1993[/TD]
[TD]8/23/1975[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Last edited:
Upvote 0
Thanks Marcelo, were getting close to the resolution: here were the results:

[TABLE="width: 315"]
<tbody>[TR]
[TD="width: 142, bgcolor: transparent"]BILLY
[/TD]
[TD="width: 106, bgcolor: transparent"]9/27/1984
[/TD]
[TD="width: 106, bgcolor: transparent"]10/9/1960
[/TD]
[TD="width: 64, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TONY
[/TD]
[TD="bgcolor: transparent"]10/3/1987
[/TD]
[TD="bgcolor: transparent"]7/1/1962
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]WILLIAM
[/TD]
[TD="bgcolor: transparent"]8/5/1989
[/TD]
[TD="bgcolor: transparent"]7/6/1967
[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TIM
[/TD]
[TD="bgcolor: transparent"]3/14/1990
[/TD]
[TD="bgcolor: transparent"]9/20/1968
[/TD]
[TD="bgcolor: transparent"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JACK
[/TD]
[TD="bgcolor: transparent"]1/10/1993
[/TD]
[TD="bgcolor: transparent"]3/20/1968
[/TD]
[TD="bgcolor: transparent"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RANDY
[/TD]
[TD="bgcolor: transparent"]1/10/1993
[/TD]
[TD="bgcolor: transparent"]5/28/1968
[/TD]
[TD="bgcolor: transparent"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BRANDON
[/TD]
[TD="bgcolor: transparent"]1/10/1993
[/TD]
[TD="bgcolor: transparent"]8/23/1975
[/TD]
[TD="bgcolor: transparent"]7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JOHN
[/TD]
[TD="bgcolor: transparent"]3/27/1993
[/TD]
[TD="bgcolor: transparent"]12/4/1976
[/TD]
[TD="bgcolor: transparent"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]KEVIN
[/TD]
[TD="bgcolor: transparent"]4/2/1993
[/TD]
[TD="bgcolor: transparent"]10/24/1976
[/TD]
[TD="bgcolor: transparent"]9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DARRYL
[/TD]
[TD="bgcolor: transparent"]5/3/1993
[/TD]
[TD="bgcolor: transparent"]2/13/1963
[/TD]
[TD="bgcolor: transparent"]10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]REGINALD
[/TD]
[TD="bgcolor: transparent"]9/7/1993
[/TD]
[TD="bgcolor: transparent"]6/19/1965
[/TD]
[TD="bgcolor: transparent"]11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ROBERT
[/TD]
[TD="bgcolor: transparent"]10/22/1993
[/TD]
[TD="bgcolor: transparent"]6/12/1974
[/TD]
[TD="bgcolor: transparent"]13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DAVID
[/TD]
[TD="bgcolor: transparent"]11/24/1993
[/TD]
[TD="bgcolor: transparent"]1/27/1962
[/TD]
[TD="bgcolor: transparent"]14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DARRYLL
[/TD]
[TD="bgcolor: transparent"]1/1/1994
[/TD]
[TD="bgcolor: transparent"]12/4/1968
[/TD]
[TD="bgcolor: transparent"]15
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TIM
[/TD]
[TD="bgcolor: transparent"]7/28/1994
[/TD]
[TD="bgcolor: transparent"]1/22/1966
[/TD]
[TD="bgcolor: transparent"]16
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Last edited:
Upvote 0
here are results further down the list:

[TABLE="width: 315"]
<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>[TR]
[TD="width: 142, bgcolor: white"]JACOB[/TD]
[TD="width: 106, bgcolor: white"]10/17/2009[/TD]
[TD="width: 106, bgcolor: white"]12/1/1988[/TD]
[TD="width: 64, bgcolor: transparent"]78[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]OTIS[/TD]
[TD="bgcolor: transparent"]11/16/2009[/TD]
[TD="bgcolor: transparent"]8/27/1975[/TD]
[TD="bgcolor: transparent"]79[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LISA[/TD]
[TD="bgcolor: transparent"]8/19/2010[/TD]
[TD="bgcolor: transparent"]1/12/1969[/TD]
[TD="bgcolor: transparent"]80[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BASHIR[/TD]
[TD="bgcolor: transparent"]2/28/2011[/TD]
[TD="bgcolor: transparent"]8/3/1971[/TD]
[TD="bgcolor: transparent"]82[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARISSA[/TD]
[TD="bgcolor: transparent"]3/22/2011[/TD]
[TD="bgcolor: transparent"]2/22/1986[/TD]
[TD="bgcolor: transparent"]83[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ANTHONY[/TD]
[TD="bgcolor: transparent"]5/13/2011[/TD]
[TD="bgcolor: transparent"]2/9/1988[/TD]
[TD="bgcolor: transparent"]85[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TOM[/TD]
[TD="bgcolor: transparent"]7/20/2011[/TD]
[TD="bgcolor: transparent"]6/29/1973[/TD]
[TD="bgcolor: transparent"]86[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]KEVIN[/TD]
[TD="bgcolor: white"]9/12/2011[/TD]
[TD="bgcolor: white"]7/20/1983[/TD]
[TD="bgcolor: transparent"]87[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]NICK[/TD]
[TD="bgcolor: transparent"]10/4/2011[/TD]
[TD="bgcolor: transparent"]12/16/1985[/TD]
[TD="bgcolor: transparent"]88[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]JUAN[/TD]
[TD="bgcolor: white"]10/10/2011[/TD]
[TD="bgcolor: white"]7/1/1981[/TD]
[TD="bgcolor: transparent"]89[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]DAMON[/TD]
[TD="bgcolor: white"]12/21/2011[/TD]
[TD="bgcolor: transparent"]6/29/1974[/TD]
[TD="bgcolor: transparent"]90[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]JACOB[/TD]
[TD="bgcolor: transparent"]12/25/2011[/TD]
[TD="bgcolor: transparent"]9/26/1990[/TD]
[TD="bgcolor: transparent"]91[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]KATE[/TD]
[TD="bgcolor: transparent"]2/27/2012[/TD]
[TD="bgcolor: transparent"]7/22/1981[/TD]
[TD="bgcolor: transparent"]92[/TD]
[/TR]
</tbody>[/TABLE]

as Shown it skipped 81 and 84
 
Upvote 0
Worked perfectly for me (data sample in post 5)

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


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

M.
 
Last edited:
Upvote 0
It seemed to work when I copied the hire date and DOB on different Columns. Thank you so much. Really appreciated it
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
C
  • Question Question
How to create a Unique Drop down of Employee names based on name and hire date, filtered by new hires and sorted
Replies
2
Views
462
cuban2
C
F
  • Solved
Excel formula needed
6 7 8
Replies
79
Views
4K
KRice
K
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
123
Fluff
Fluff
SaraWitch
  • Solved
Calculate Target Date based on DDL option
Replies
5
Views
242
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top