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.
|