Sum FTEs based on start/end date

[JoeCam]

I have two tabs of data. On my "Labor" tab I have a list of employee start & end dates, FTE counts & position titles.

On my "Budget" tab, I have the same job levels in column A and the first day of each month across row 1 (starting in column B),

I'm trying to write a formula that will populate the number of FTEs into each column on the Budget tab based on their start/end date.

This formula is working for the first column, but when I drag it over to February and beyond it doesn't work for me. Also, I'm sure it won't calculate the correct number of FTEs if a resource leaves in the middle of the month, for example. My FTE calculations are based off of calendar days, not business days.

Can anyone assist?

 

[etaf]

not sure that should have worked in the first cell
as column D is 2030 - so NOT less than eomonth(1/1/24,0)

=SUMIFS(labour!$E$2:$E$100,labor!$H$2:$H$100,$A3,labor!$D$2:$D$100,">="&B$1,$C$2:$C$100,"<="&EOMONTH(B$1,0))

I have added into one sheet for XL2BB to show easier

Book1
	A	B	C	D	E	F	G	H	I	J
1	start	end	fte	postion			1/1/24	2/1/24	3/1/24	4/1/24
2	1/1/24	3/18/24	1	manager		stocker	0	1	1	0
3	1/1/24	12/31/30	1	tech II		tech II	1	1	1	1
4	1/1/24	12/31/30	1	Laborer		Tech 1	0	0	0	0
5	1/1/24	12/31/30	1	Laborer		Manager	1	1	1	0
6	1/1/24	12/31/30	1	Laborer		Laborer	3	3	3	3
7	2/28/24	3/12/24	1	stocker
8
9	0.069	0.39
Sheet1

Cell Formulas
Range	Formula
G2:J6	G2	=SUMIFS($C$2:$C$100,$D$2:$D$100,$F2,$B$2:$B$100,">="&G$1,$A$2:$A$100,"<="&EOMONTH(G$1,0))
A9	A9	=2/29
B9	B9	=12/31

not sure how to work out the FTE as that is summarised per month , but the data sheet shows 1 , for the full employment period

so for JAN 24
if they left on 10/1/24 - then you want a fraction for that month

for Stocker in my example
then for Feb , as started on 28/2/24 (leap year) -
so 2 days out of 29 days = 0.069 FTE reported in FEB
and in Mar 12/31 = 0.39 FTE reported in MAR

as i say not sure how to do that

 

[JoeCam]

not sure that should have worked in the first cell
as column D is 2030 - so NOT less than eomonth(1/1/24,0)

not sure how to work out the FTE as that is summarised per month , but the data sheet shows 1 , for the full employment period

so for JAN 24
if they left on 10/1/24 - then you want a fraction for that month

for Stocker in my example
then for Feb , as started on 28/2/24 (leap year) -
so 2 days out of 29 days = 0.069 FTE reported in FEB
and in Mar 12/31 = 0.39 FTE reported in MAR

as i say not sure how to do that

Thank you, I did get it to work with the formula you provided! A major problem I had (and probably why it was working in the first column for me) was my "end date" was set to 1930 instead of 2030...

Once I got that corrected your formula worked perfectly. I can always work on the partial FTE calculations later.

 

[etaf]

ok,
you are welcome