Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-21-2016, 06:20 AM
CPSmith CPSmith is offline Windows XP Office 2013
Novice
 
Join Date: Feb 2016
Posts: 3
CPSmith is on a distinguished road
Default 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.
Attached Files
File Type: xlsx Turnover Calculation 2016 for post.xlsx (68.8 KB, 25 views)
File Type: xls Turnover for Post.xls (59.0 KB, 20 views)
Reply With Quote
  #2  
Old 03-26-2016, 02:48 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,005
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Not sure if I have understood you correct, but you might want to take at look at the attached. In particular I don't know how you distinguish between Department 1, Department 2 and Department 3.

I have combined your two files into one and put some formulas in sheet Full Time Turnover by Month (and also a Pivot table).

Please note that status, FT.PT and status.Date are defined names (See Name Manager).

You can change the year in cell A3.
Attached Files
File Type: xlsx Turnover Calculation_2.xlsx (137.0 KB, 46 views)

Last edited by xor; 03-26-2016 at 06:40 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Graph occurences over full range of time? pixeldroid Excel 1 07-13-2015 09:30 AM
Next to the resource bar Part-time% BuffLouis Project 3 08-18-2014 06:05 AM
Count the common time period (month) between two date period of time Barni Excel 6 08-15-2014 07:52 AM
Employee time tracking using email iampav Outlook 1 06-07-2012 06:54 AM
Part Time Resource Query iamthequinn Project 1 02-06-2012 08:15 AM


All times are GMT -7. The time now is 07:24 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft