Calculating values meeting multiple conditions

I want to calculate the number of full time employees (FTE) at our company for every year since 2004.

My spreadsheet includes every employee who has ever worked at our company, the date they started (Start Date) and, for some, the date they left the company (End Date). If they are still with the company, the End Date is blank.

I would like to generate a report showing the number of Full Time Employees (FTE's) by year, including the fraction of the year they joined or left. For example, if someone joined in June, 2004 and left in Sept 2010, They would count as ½ of FTE for the year 2004, 1 FTE for 2005 through 2009 and ¾ for 2010.

I've started by creating columns for each year 2004, 2005, etc. But, I'm struggling to create a formula to test for all possible conditions.

• With company at all for the year?
• If no, enter 0.
• If yes, what enter the fraction of the year based on either the Start Date or End Date?

Any ideas? I must be over-complicating this.

My data is organized as you suggest. Sorry if I gave a different impression. I hope you are right about the calculations being simple but so far, they're beyond me.

Assume the structure you propose plus separate columns for 2004, 2005, ... 2013.

How would you calculate the number of FTE's in each column / row intersection?

Thanks for any guidance.

-

Originally Posted by newdoverman

I think that you should try organizing your data something like this.

Column A: Name
Column B: Start Date
Column C: End Date
Column D: Length of service.

Use proper Excel recognizable dates (don't put two dates in one column...put Start and End dates in their own columns) and calculations will be relatively simple.

Thanks for your help on this. I suspect you are correct about me overcomplicating this.

The attached shows some sample data in Start Date and End Date. The columns for 2004 - 2013 are blank.

The goal is to generate a report showing the number of FTE's in each year.

Thanks again.

Workbook1.xlsx

Originally Posted by newdoverman

Can you post a representative workbook with realistic data as I think that you are making it more complicated than it should be by adding columns for each year?

Thank you, very much. It worked.

Even after studying your solution for a few minutes, I don't see how the fractional portions of the years are being calculated. They are being calculated but I don't see how.

Thanks again for you help on this.