• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula/function needed for calculating employee's year, month, day at specific rank, like Patrol, Corporal, Sgt., etc., for seniority.

cricket1001

cricket1001

Member
I have been searching and trying numerous ways to find a formula, function to use to determine employee's seniority by year, month, & day according to their rank.
I've tried using IF function, but was confused in how to put a DATEDIF inside an IF function.

Thanks
 
Are you trying to compare the seniority of different employees or simply achieve a table showing the seniority of each employee at a given date? The first can be done by measuring seniority in days
= today - [@ApptDate]
Such measures can be compared and differences calculated, positive or negative.
If the objective is to get a pretty layout, then the formulas
= DATEDIF( [@ApptDate], today, "y" )
= DATEDIF( [@ApptDate], today, "ym" )
= DATEDIF( [@ApptDate], today, "md" )
give the values, though the days can occasionally be incorrect.
 
I am so sorry that I haven't returned to this forum and thread until now.
I am trying to show officers seniority in the specific rank. So that when there are 5 sergeants and 5 corporals, I can see who is the senior sergeant, etc. I have a formula for finding their seniority by date of hire but I think it would be good to show the seniority in their rank.

Actually, Peter, after seeing what you suggested and looking at what I have been using for seniority ranking by date of hire, I only have the seniority by the year and that is not totally accurate. It should be by the month, day and year to get an accurate seniority ranking.

I am attaching part of my worksheet for seniority. Names have been changed to protect the innocent. (I think from intro to TV show Dragnet. ;) )

Thanks for any help y'all can give me!!!
 
If I am understanding correctly, then

Data Range
K​
L​
10​
Seniority
By DOH​
Seniority
By DOP​
11​
16​
6​
12​
23​
​
13​
10​
3​
14​
6​
8​
15​
22​
​
16​
14​
​
17​
2​
​
18​
3​
​
19​
13​
4​
20​
12​
1​
21​
11​
6​
22​
1​
​
23​
26​
​
24​
21​
​
25​
19​
​
26​
8​
9​
27​
24​
​
28​
25​
​
29​
20​
10​
30​
9​
​
31​
5​
​
32​
18​
​
33​
7​
​
34​
4​
2​
35​
15​
​
36​
17​
5​

Data Range
K​
L​
10​
Seniority
By DOH​
Seniority
By DOP​
11​
=RANK(F11,$F$11:$F$36,1)​
=IFERROR(RANK(I11,$I$11:$I$36,1),"")​
12​
=RANK(F12,$F$11:$F$36,1)​
=IFERROR(RANK(I12,$I$11:$I$36,1),"")​
13​
=RANK(F13,$F$11:$F$36,1)​
=IFERROR(RANK(I13,$I$11:$I$36,1),"")​
14​
=RANK(F14,$F$11:$F$36,1)​
=IFERROR(RANK(I14,$I$11:$I$36,1),"")​
15​
=RANK(F15,$F$11:$F$36,1)​
=IFERROR(RANK(I15,$I$11:$I$36,1),"")​
16​
=RANK(F16,$F$11:$F$36,1)​
=IFERROR(RANK(I16,$I$11:$I$36,1),"")​
17​
=RANK(F17,$F$11:$F$36,1)​
=IFERROR(RANK(I17,$I$11:$I$36,1),"")​
18​
=RANK(F18,$F$11:$F$36,1)​
=IFERROR(RANK(I18,$I$11:$I$36,1),"")​
19​
=RANK(F19,$F$11:$F$36,1)​
=IFERROR(RANK(I19,$I$11:$I$36,1),"")​
20​
=RANK(F20,$F$11:$F$36,1)​
=IFERROR(RANK(I20,$I$11:$I$36,1),"")​
21​
=RANK(F21,$F$11:$F$36,1)​
=IFERROR(RANK(I21,$I$11:$I$36,1),"")​
22​
=RANK(F22,$F$11:$F$36,1)​
=IFERROR(RANK(I22,$I$11:$I$36,1),"")​
23​
=RANK(F23,$F$11:$F$36,1)​
=IFERROR(RANK(I23,$I$11:$I$36,1),"")​
24​
=RANK(F24,$F$11:$F$36,1)​
=IFERROR(RANK(I24,$I$11:$I$36,1),"")​
25​
=RANK(F25,$F$11:$F$36,1)​
=IFERROR(RANK(I25,$I$11:$I$36,1),"")​
26​
=RANK(F26,$F$11:$F$36,1)​
=IFERROR(RANK(I26,$I$11:$I$36,1),"")​
27​
=RANK(F27,$F$11:$F$36,1)​
=IFERROR(RANK(I27,$I$11:$I$36,1),"")​
28​
=RANK(F28,$F$11:$F$36,1)​
=IFERROR(RANK(I28,$I$11:$I$36,1),"")​
29​
=RANK(F29,$F$11:$F$36,1)​
=IFERROR(RANK(I29,$I$11:$I$36,1),"")​
30​
=RANK(F30,$F$11:$F$36,1)​
=IFERROR(RANK(I30,$I$11:$I$36,1),"")​
31​
=RANK(F31,$F$11:$F$36,1)​
=IFERROR(RANK(I31,$I$11:$I$36,1),"")​
32​
=RANK(F32,$F$11:$F$36,1)​
=IFERROR(RANK(I32,$I$11:$I$36,1),"")​
33​
=RANK(F33,$F$11:$F$36,1)​
=IFERROR(RANK(I33,$I$11:$I$36,1),"")​
34​
=RANK(F34,$F$11:$F$36,1)​
=IFERROR(RANK(I34,$I$11:$I$36,1),"")​
35​
=RANK(F35,$F$11:$F$36,1)​
=IFERROR(RANK(I35,$I$11:$I$36,1),"")​
36​
=RANK(F36,$F$11:$F$36,1)​
=IFERROR(RANK(I36,$I$11:$I$36,1),"")​
 
Does this work? often don't travel well between the UK and US!
I have used single-cell array formulas for the seniorities but it could be avoided.
 

Attachments

  • PD_Seniority_Test.xlsx
    23.5 KB · Views: 2
No. The formula you are using for Rank by DOH has Officer George Burns, F22, who has worked only 6 years as #1 in seniority. But actually Sgt. Joe Seattle, F11, should be #1 with 26 years working here.
For Rank by DOP, I want the seniority by each rank, so there are 5 sergeants and those 5 should be ranked in order of their promotion date. Now I didn't include date of promotion for all officers because I don't know some of them. I should have put in fake dates for that. Sorry!!! Let me fill in those date and attach the updated file.
 

Attachments

  • PD_Seniority_Test.xlsx
    19.5 KB · Views: 3
Sorry, Peter, my last comment was for Alan.
Peter, when I opened your attachment the formulas in both Seniority by DOH and By DOP came out showing #NAME?. Is the formula in Seniority By DOH correct? ( = _xlfn.TEXTJOIN( ", ", 1, DATEDIF( [@DOH], today, {"Y";"YM";"MD"} ) & {"yr";"mth";"days"} )} ) Then the formula in Seniority by DOP, ( = _xlfn.TEXTJOIN( ", ", 1, DATEDIF( IF([@DOP],[@DOP],[@DOH]), today, {"Y";"YM";"MD"} ) & {"yr";"mth";"days"} )} ), correct? Is this how you got your report? Why doesn't it work in my spreadsheet with me getting #NAME? ?
In looking at the report you showed in your next column, that looks like what I need for Seniority by DOH and then I would have to sort them from largest to smallest or use the RANK function. But for Seniority by DOP, like I said in my comment to Alan, I need to have each Rank sorted by the years in that rank and then show the seniority only for that particular rank. I was thinking that since the ranks are all color coordinated all of the Sgt. ranks (5) would have numbers 1-5. Same for each rank. Also, when I put the filter on all the cells, when I choose Sgts then only 5 rows would appear and they would be ranked 1-5.

I'm sorry I haven't explained myself to well for this.
 
Cricket, Attached is your first file which have people in a different sort order than the second. I have Joe Seattle as Nr 1.
 

Attachments

  • PD_Seniority_Test.xlsx
    29.3 KB · Views: 1
@cricket1001
You clearly do not have the function TEXTJOIN (Office 2019 and 365) so I have removed the arrays and concatenated each term.
= DATEDIF( [@[Seniority date]], today, "y" ) & "yr, " &
DATEDIF( [@[Seniority date]], today, "ym" ) & "mth, " &
DATEDIF( [@[Seniority date]], today, "md" ) & "days"
I have also provided seniority within each rank.
= 1 + COUNTIFS( [Rank],[@Rank], [Seniority date], "<"&[@[Seniority date]] )
and introduced a numeric grade to assist sorting on rank
= LOOKUP([@Rank], Rank, Grade)

70517
 

Attachments

  • PD_Seniority_Test.xlsx
    25.6 KB · Views: 1
@AlanSidman
As you are using PQ, you could sort the problem of Corporals being listed between the Chief and his Deputy. Perhaps a swift inner join? Not that I was sure of where to put the detectives or the one-man dog section.
 
You must log in or register to reply here.
Back
Top