• 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
[Table="class: grid"][tr][td] [/td][td]
K​
[/td][td]
L​
[/td][/tr]
[tr][td]
10​
[/td][td]
Seniority
By DOH​
[/td][td]
Seniority
By DOP​
[/td][/tr]

[tr][td]
11​
[/td][td]
16​
[/td][td]
6​
[/td][/tr]

[tr][td]
12​
[/td][td]
23​
[/td][td]
​
[/td][/tr]

[tr][td]
13​
[/td][td]
10​
[/td][td]
3​
[/td][/tr]

[tr][td]
14​
[/td][td]
6​
[/td][td]
8​
[/td][/tr]

[tr][td]
15​
[/td][td]
22​
[/td][td]
​
[/td][/tr]

[tr][td]
16​
[/td][td]
14​
[/td][td]
​
[/td][/tr]

[tr][td]
17​
[/td][td]
2​
[/td][td]
​
[/td][/tr]

[tr][td]
18​
[/td][td]
3​
[/td][td]
​
[/td][/tr]

[tr][td]
19​
[/td][td]
13​
[/td][td]
4​
[/td][/tr]

[tr][td]
20​
[/td][td]
12​
[/td][td]
1​
[/td][/tr]

[tr][td]
21​
[/td][td]
11​
[/td][td]
6​
[/td][/tr]

[tr][td]
22​
[/td][td]
1​
[/td][td]
​
[/td][/tr]

[tr][td]
23​
[/td][td]
26​
[/td][td]
​
[/td][/tr]

[tr][td]
24​
[/td][td]
21​
[/td][td]
​
[/td][/tr]

[tr][td]
25​
[/td][td]
19​
[/td][td]
​
[/td][/tr]

[tr][td]
26​
[/td][td]
8​
[/td][td]
9​
[/td][/tr]

[tr][td]
27​
[/td][td]
24​
[/td][td]
​
[/td][/tr]

[tr][td]
28​
[/td][td]
25​
[/td][td]
​
[/td][/tr]

[tr][td]
29​
[/td][td]
20​
[/td][td]
10​
[/td][/tr]

[tr][td]
30​
[/td][td]
9​
[/td][td]
​
[/td][/tr]

[tr][td]
31​
[/td][td]
5​
[/td][td]
​
[/td][/tr]

[tr][td]
32​
[/td][td]
18​
[/td][td]
​
[/td][/tr]

[tr][td]
33​
[/td][td]
7​
[/td][td]
​
[/td][/tr]

[tr][td]
34​
[/td][td]
4​
[/td][td]
2​
[/td][/tr]

[tr][td]
35​
[/td][td]
15​
[/td][td]
​
[/td][/tr]

[tr][td]
36​
[/td][td]
17​
[/td][td]
5​
[/td][/tr]
[/table]

Data Range
[Table="class: grid"][tr][td] [/td][td]
K​
[/td][td]
L​
[/td][/tr]
[tr][td]
10​
[/td][td]
Seniority
By DOH​
[/td][td]
Seniority
By DOP​
[/td][/tr]

[tr][td]
11​
[/td][td]
=RANK(F11,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I11,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
12​
[/td][td]
=RANK(F12,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I12,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
13​
[/td][td]
=RANK(F13,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I13,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
14​
[/td][td]
=RANK(F14,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I14,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
15​
[/td][td]
=RANK(F15,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I15,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
16​
[/td][td]
=RANK(F16,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I16,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
17​
[/td][td]
=RANK(F17,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I17,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
18​
[/td][td]
=RANK(F18,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I18,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
19​
[/td][td]
=RANK(F19,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I19,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
20​
[/td][td]
=RANK(F20,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I20,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
21​
[/td][td]
=RANK(F21,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I21,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
22​
[/td][td]
=RANK(F22,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I22,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
23​
[/td][td]
=RANK(F23,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I23,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
24​
[/td][td]
=RANK(F24,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I24,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
25​
[/td][td]
=RANK(F25,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I25,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
26​
[/td][td]
=RANK(F26,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I26,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
27​
[/td][td]
=RANK(F27,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I27,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
28​
[/td][td]
=RANK(F28,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I28,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
29​
[/td][td]
=RANK(F29,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I29,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
30​
[/td][td]
=RANK(F30,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I30,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
31​
[/td][td]
=RANK(F31,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I31,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
32​
[/td][td]
=RANK(F32,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I32,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
33​
[/td][td]
=RANK(F33,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I33,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
34​
[/td][td]
=RANK(F34,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I34,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
35​
[/td][td]
=RANK(F35,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I35,$I$11:$I$36,1),"")​
[/td][/tr]

[tr][td]
36​
[/td][td]
=RANK(F36,$F$11:$F$36,1)​
[/td][td]
=IFERROR(RANK(I36,$I$11:$I$36,1),"")​
[/td][/tr]
[/table]
 
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

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.
 
@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

@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