1 Reply Latest reply on Feb 13, 2019 7:25 AM by Hunter Morgan

    How to calculate tenure of staff with multiple hire dates?

    Kyle Mickalowski

      Goal: calculate tenure of any staff member as well as determining the average tenure of all current staff members at any given time.

       

      I have a table with one row per day per employee based on their hiring (Date From) and termination dates (Date To). I planned on using RUNNING_SUM to calculate tenure. This works when looking at one employee but fails when multiple staff are included because the days worked by terminated staff are still included in the calculation. Another complication is some staff have multiple hire and termination dates. I do not want to count their length of service when they are no longer with the company but want it included again if they return.


      The screenshot below shows what my data looks like for two staff members. The 'Total Staff' column fluctuates from 1 to 2 as one person is hired, leaves, returns, and leaves again. The blue highlighted columns show how I want the calculation to work.

       

      Desired Output.png


      I have attached a workbook with the data and would appreciate any thoughts/advice on how to achieve the desired output. Thanks in advance for your help!

        • 1. Re: How to calculate tenure of staff with multiple hire dates?
          Hunter Morgan

          Interesting question Kyle. I took a different approach to a similar problem.

           

          I calculated "Length of service" as such:

           

          DATEDIFF ('month', [Hire Date], Today())

           

          I don't think you want to be creating separate spreadsheets manually, so tableau can help you there.

           

          As for your rehire date, you'd have to think of the logic and I haven't had the time to go into your workbook to work that out. Your comment was made ages ago, so let me know if you are still needing assistance with this.