Employee Turnover by Department/Month split between Voluntary/Involuntary - Full-time/Part-time
Hello
I'm not sure if this belongs in regular excel or the excel programming -- so I put it here thinking there may be a macro solution.
I am trying to devise a turnover report that will allow me to report out turnover by month for 2014/2015 and 2016 as managers are being asked to improve turnover rate for high performers.
In order to do a calculation on turnover I have to have the # of employees in the month and the # of employees that terminated in the month. Since we don't have archive reports where I can run this for each month/each year - I have to figure out a way to do it with this current report of active/terminated employees.
I thought about adding a column for each month and idicating if they were employed in that month based on their hire date/term date - if applicable -- but that seems to be a very manual process when I have over 2000 lines of data. I have been able to do some annual calculations using pivot tables -- but that doesn't help with the monthly numbers. I also thought of trying to use the years of service calcualtion where years of service is greater than 1 year as counting towards every month's employment totals in 2015....but I'm not sure I'm thinking about this in the best way.
Any ideas on how to organize the data to produce this type of report?
I have attached two files below -- one in a subset of my current data -- the other is the desired layout of the results. The desired layout is just a mock up and can definitely change to make this an easy process. If this is successful my guess is they will start using it monthly.
Thanks in advance for any assistance/suggestions you might have.
|