Leigh Digons
Member
I am looking for help to set up a spreadsheet to find the FTE for an employee in a particular dept, location, title for each month. Transfers of dept, location, or title can happen any day of any month for 2015. The FTE is the portion of the month in that combination of categories divided by the days of the month.
Here's an example: An Elf is hired to work in Toys on 1/3/2015. He changes departments to Games on 1/15/2015. Then he takes a Leave of Absence from 1/17 to 1/19, and another Leave of Absence from 1/21 to 1/23. Then he gets promoted on 1/29/2015 to Senior Elf. Then he is terminated on 1/30/2015.
So for 12 days in January he is an Elf who works in Toys.
For 10 days in January he is an Elf who worked in Games. (you subtract the LOA days)
And then for 2 days in January, he is a Senior Elf works in Games.
4 days of January he is on LOA so not counted
And 2 days of January he is not yet hired
For 1 day in January he is not working because he was terminated (the 30th is the last day in the office)
FTE for Elf/Toys .39 which is 12 divided by 31
FTE for Elf/Games .32 which is 10 divided by 31
FTE for Senior Elf/Games .06 which is 2 divided by 31
He could also change Locations during the month, which would usually (if not always) be the same date as the Department change. So on 1/15/2015 he might also change Locations to London from NY.
In addition, there could be changes in FTE rate. So most of the time the FTE rate is 100%. But if a person is on part-time status, the FTE rate could go down to 70 or 80% on the first of the current month or the next month (1st-15th the change is recorded on first of current month, 16th-31st, the change is recorded on the first of the following month).
So in the scenario above, the FTE for Elf/Toys would be .39 multiplied by the FTE rate of 70% (given) for January.
If additional info is needed let me know. The first tab can be fixed later, but I am using the second tab to lay out the calculations. The FTE rate calculation and multiplication can be done on the first tab as well to make the 2nd tab less complicated. Would like to use the 2nd tab for any transfers and calculate the FTE rate changes in a separate table which is easier for me.
Here's an example: An Elf is hired to work in Toys on 1/3/2015. He changes departments to Games on 1/15/2015. Then he takes a Leave of Absence from 1/17 to 1/19, and another Leave of Absence from 1/21 to 1/23. Then he gets promoted on 1/29/2015 to Senior Elf. Then he is terminated on 1/30/2015.
So for 12 days in January he is an Elf who works in Toys.
For 10 days in January he is an Elf who worked in Games. (you subtract the LOA days)
And then for 2 days in January, he is a Senior Elf works in Games.
4 days of January he is on LOA so not counted
And 2 days of January he is not yet hired
For 1 day in January he is not working because he was terminated (the 30th is the last day in the office)
FTE for Elf/Toys .39 which is 12 divided by 31
FTE for Elf/Games .32 which is 10 divided by 31
FTE for Senior Elf/Games .06 which is 2 divided by 31
He could also change Locations during the month, which would usually (if not always) be the same date as the Department change. So on 1/15/2015 he might also change Locations to London from NY.
In addition, there could be changes in FTE rate. So most of the time the FTE rate is 100%. But if a person is on part-time status, the FTE rate could go down to 70 or 80% on the first of the current month or the next month (1st-15th the change is recorded on first of current month, 16th-31st, the change is recorded on the first of the following month).
So in the scenario above, the FTE for Elf/Toys would be .39 multiplied by the FTE rate of 70% (given) for January.
If additional info is needed let me know. The first tab can be fixed later, but I am using the second tab to lay out the calculations. The FTE rate calculation and multiplication can be done on the first tab as well to make the 2nd tab less complicated. Would like to use the 2nd tab for any transfers and calculate the FTE rate changes in a separate table which is easier for me.