Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

How can I calculate an employee's ID number based on their hire date?

I have a list of employees and want to generate an "Employee ID" based on the Hire Date value. I was hoping to be able to check the Hire Date value and compare it to an array of all the hire dates and to return the correct number.

You can see a list of these dates here: https://docs.google.com/spreadsheets/d/1ogjWzFPWLUECIP9YXL-r7RHM-hPWTPZ-6wX0sTV0QNc/edit?usp=sharing

Ideally, (using a small sampling of the dates above) it would look like the following:

    Hire Date     Employee ID
    3/6/2012          1
    3/30/2015         4
    8/10/2015         5
    8/10/2015         6
    9/18/2015         7
    9/18/2015         8
    6/26/2020         9
    3/6/2012          2
    2/7/2013          3
Solution

  • use in B1:

    ={"ID"; ARRAYFORMULA(IFNA(VLOOKUP(A2:A, {SORT(A2:A), ROW(A2:A)-1}, 2, 0)))}

    or:

    =ARRAYFORMULA(RANK(A2:A, A2:A, 1))

    if you want it to not repeat on the same date use:

    ={"ID"; ARRAYFORMULA(IF(A2:A="",,IFNA(VLOOKUP(A2:A&"z"&
 COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)), {SORT({A2:A&"z"& 
 COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))}), ROW(A2:A)-1}, 2, 0))))}

    0