Help showing active months between two dates
 

I am completely stumped while trying to find the active months between two dates for an employee turnover rate. For example if "Ann" was hired 12/9/11 and quit 2/18/12 she would be considered an active employee for 3 months (Dec 11, Jan 12, Feb 12). My list is currently set up like this .....

Name.........Start Date.........End Date
Ann............12/9/11............2/18/12
Bob.............11/6/11............12/6/11
Carl.............12/28/11..........1/15/12

So from the data above I can say

Ann was active.............(Dec 11, Jan 12, Feb 12)
Bob was active (Nov 11, Dec 11)
Carl was active.............(Dec 11, Jan 12)

I am not sure how to show THIS ^ on excel!



The main goal is to have the data in the end to look like this:

2011
Month #Term #Active Turnover Rate
....
Nov .........0..........1..........0% (=b3/c3)
Dec..........1.........3...........33%

2012
Month #Term #Active Turnover Rate
Jan.........1........2............50%
Feb.........1........1...........100%
....

When I try a pivot with the start date it only gives me the number of employees that started that month/year and does not show those who roll over to the next month. This is all very confusing and will possibly require more than one step to solve. A big thank you in advance.

Not sure if you can display the difference in months as text. You could use a DATEDIF to work it as a numerical value. I.E... =DATEDIF(B2,B3,"M")

Yes but when I use this formula it only counts WHOLE months

For example if it were 09/25..... 11/3 the =dateif would only read as 1
I would need it to read as 3 because for this purposes they were with us 3 months

A very simplified way of doing it, but I think this should work for you...

Yes, that works great for giving me the number of months!

Now I would like to have a text value associated with it. So the 3 months from September 2011-November 2011 would say something like
A1..........B1.........C1
Sept 11', Oct 11', Nov 11'

So i can then add all the Sept 11' column to see how many people were employed in September of 2011

Well that can be done too, but I can't help feeling there's an easier/better way around that...

Sample data would be a good idea so I could demonstrate my idea easily...

pubnut @ gmail . com if you don't want to post data here...

Email sent

Reply email sent, requiring further information.