Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Quote:
|
#3
|
|||
|
|||
Quote:
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 |
#4
|
|||
|
|||
A very simplified way of doing it, but I think this should work for you...
|
#5
|
|||
|
|||
Quote:
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 |
#6
|
|||
|
|||
Quote:
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... |
#7
|
|||
|
|||
Email sent
|
#8
|
|||
|
|||
Reply email sent, requiring further information.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help showing active months between two dates | Excel Discussion (Misc queries) | |||
Showing Lists in active cell | Excel Discussion (Misc queries) | |||
Stop dates from showing as numbers - when formated as dates | Excel Discussion (Misc queries) | |||
Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
Dates to months and calculating values for their months | Excel Programming |