Calculating FTE using two dates and STD hours

[Kmitchell]

Looking for guidance on best formula to use to calculate FTEs. If I know the Hire date and end date and standard hours what is the best formula to use to compute this? In some examples if there is no end date the employee is still active. Thanks!

A B C
1 STD HOURS HIRE DATE END DATE
2 40.00 8/1/15 11/30/15
3 24.00 9/15/15

 

[rforster]

Look at your data. If someone is standard 40 hours their FTE value will be 1. IF someone works 24 hours, .6 or whatever the percent is. Like:
Make a column for FTE value. The enter =if(a2=40, 1, a2/40) Copy it down the column and sum.

I don't know how hire or end date would matter. This give you a calculation of what your FTE was at the time the data was made available.

 

[rforster]

If you are after FTE with no End date, or active employees, filter on the end date and choose the blanks. Copy and paste into a new sheet and use that formula.

 

[jorismoerings]

Hi,

1. Use networkdays(start date,end date,[holidays]) to calculate number of workdays where start date is the cell containing the start date and end date the cell containing the end date
2. Convert your column of standard hours to Fte% per week

formula would look like something like this

=NETWORKDAYS(B2,IF(ISBLANK(C2),TODAY(),C2))*(A2/40)

That's assuming the standard hours are related to number of hours per workweek

 

[jorismoerings]

Hi,

Forgot in my reply to add the calculation back to the FTE number.
This should do the trick:

	A	B	C	D
STD HOURS	HIRE DATE	END DATE

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]FTE[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]40:00:00[/TD]
[TD="align: right"]8-1-2015[/TD]
[TD="align: right"]30-11-2015[/TD]
[TD="align: center"]1,34[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]24:00:00[/TD]
[TD="align: right"]15-9-2014[/TD]
[TD="align: right"][/TD]
[TD="align: center"]0,83[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=(NETWORKDAYS(B2,IF(ISBLANK(C2),TODAY(),C2))*((A2*24)/40))/173.33
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=(NETWORKDAYS(B3,IF(ISBLANK(C3),TODAY(),C3))*((A3*24)/40))/173.33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The formula part ((A2*24)/40)) is added because i was unsure if the standard hours in column A where numerical formatted or as Time formatted.
My example above is formatted as time.

 

[Kmitchell]

Thank you for your reply. My apologies I don't think I was clear in my request. I'm trying to compute the annual FTE each year. If a person in 2015 only wo6rks 3 months and at reduced hours so their FTE will be pro-rated based on those two factors. In the example shown in D2 the FTE should be .33 of an FTE. The standard hours represents 40 hours worked per week. I think I need to factor in the year in the calc somewhere so that it knows that an FTE can not be greater than 2015. My current schedule is looking to calculate the FTEs for this calendar year and 2016. Does this make more sense?

 

[jorismoerings]

Hi,

Try these steps:

1. determine # worked days
2. determine annual # worked hours 1 standard FTE
3. determine the Part Time factor (PT)
4. determine # PT Annual worked hrs
5. calculate FTE

Book1
	A	B	C	D	E	F	G	H	I	J
1	Base number STD Hours for 1 FTE p/year				2080
2	Base number STD Hours for 1 FTE p/workday				8
3
4	STD HOURS	HIRE DATE	END DATE	Annual FTE		Step 1	Step 2	Step 3	Step 4	Step 5
5	40:00:00	1-8-2015	30-11-2015	0,33		86	688	1,00	688	0,33
6	24:00:00	15-9-2015				0	0	0,60	-	-
Sheet1

Cell Formulas
Range	Formula
D5	=(IF(B5>TODAY(),0,NETWORKDAYS(B5,IF(ISBLANK(C5),TODAY(),C5))))*$E$2*(A5*24)/($E$1/52)/$E$1
F5	=(IF(B5>TODAY(),0,NETWORKDAYS(B5,IF(ISBLANK(C5),TODAY(),C5))))
F6	=(IF(B6>TODAY(),0,NETWORKDAYS(B6,IF(ISBLANK(C6),TODAY(),C6))))
G5	=F5*$E$2
G6	=F6*$E$2
H5	=(A5*24)/($E$1/52)
H6	=(A6*24)/($E$1/52)
I5	=H5*G5
I6	=H6*G6
J5	=I5/E1
J6	=I6/E2