FTE based on start & end date

[s_1592]

Hi there,
I'm trying to calculate the monthly FTE (full-time equivalent) for an employee based on the start date & end date.

Let's assume this person started working on 01/15/2022 and was terminated on 10/13/2023. I need to calculate the monthly FTE (from 0 to 1) based on calendar days, so that:

• Prior to 2022, FTE= 0;
• Jan 2022 FTE=~.49 (i.e. 15/31)
• Feb 22-Sep 23, FTE=1;
• Oct 23, FTE=~.42 (i.e., 13/31);
• from Nov 23 onward, FTE= 0

Assume the Start date is in cell A2, the end date is in B2, and the month headers start from C1.

Your help would be greatly appreciated. I've tried over and over and haven't yet been able to crack the code.
Thanks!

 

[s_1592]

Edit: actually Jan 22’s FTE would be equal to ~.55 (i.e., 17/31 or (31-15)/31).

 

[JamesCanale]

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1	Name	Start	Finish	Jul 2021	Aug 2021	Sep 2021	Oct 2021	Nov 2021	Dec 2021	Jan 2022	Feb 2022	Mar 2022	Apr 2022	May 2022	Jun 2022	Jul 2022	Aug 2022	Sep 2022	Oct 2022	Nov 2022	Dec 2022	Jan 2023	Feb 2023	Mar 2023	Apr 2023	May 2023	Jun 2023	Jul 2023	Aug 2023	Sep 2023	Oct 2023	Nov 2023	Dec 2023	Jan 2024	Feb 2024	Mar 2024	Apr 2024	May 2024	Jun 2024	Jul 2024	Aug 2024	Sep 2024
2	Fred	1/15/2022	10/13/2023	0.00	0.00	0.00	0.00	0.00	0.00	0.55	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	0.42	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3	Harry	1/1/2022	6/30/2022	0.00	0.00	0.00	0.00	0.00	0.00	1.00	1.00	1.00	1.00	1.00	1.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
4	Jamie	2/5/2022	2/25/2022	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.75	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Sheet26

Cell Formulas
Range	Formula
E1:AP1	E1	=EDATE(D1,1)
D2:AP4	D2	=IF(OR(D$1>EOMONTH($C2,0),D$1<EOMONTH($B2,-1)+1),0,IF(AND(D$1>EOMONTH($B2,0),D$1<EOMONTH($C2,-1)),1,(1+MIN(EOMONTH(D$1,0),$C2)-MAX(EOMONTH(D$1,-1)+1,$B2))/(EOMONTH(D$1,0)-EOMONTH(D$1,-1))))

 

[Tetra201]

Try placing the following formula in cell C2 ad drag-copying it to the right as needed:

=MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2)+1)/DAY(EOMONTH(C$1,0))

 

[s_1592]

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1	Name	Start	Finish	Jul 2021	Aug 2021	Sep 2021	Oct 2021	Nov 2021	Dec 2021	Jan 2022	Feb 2022	Mar 2022	Apr 2022	May 2022	Jun 2022	Jul 2022	Aug 2022	Sep 2022	Oct 2022	Nov 2022	Dec 2022	Jan 2023	Feb 2023	Mar 2023	Apr 2023	May 2023	Jun 2023	Jul 2023	Aug 2023	Sep 2023	Oct 2023	Nov 2023	Dec 2023	Jan 2024	Feb 2024	Mar 2024	Apr 2024	May 2024	Jun 2024	Jul 2024	Aug 2024	Sep 2024
2	Fred	1/15/2022	10/13/2023	0.00	0.00	0.00	0.00	0.00	0.00	0.55	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	0.42	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3	Harry	1/1/2022	6/30/2022	0.00	0.00	0.00	0.00	0.00	0.00	1.00	1.00	1.00	1.00	1.00	1.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
4	Jamie	2/5/2022	2/25/2022	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.75	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
Sheet26

Cell Formulas
Range	Formula
E1:AP1	E1	=EDATE(D1,1)
D2:AP4	D2	=IF(OR(D$1>EOMONTH($C2,0),D$1<EOMONTH($B2,-1)+1),0,IF(AND(D$1>EOMONTH($B2,0),D$1<EOMONTH($C2,-1)),1,(1+MIN(EOMONTH(D$1,0),$C2)-MAX(EOMONTH(D$1,-1)+1,$B2))/(EOMONTH(D$1,0)-EOMONTH(D$1,-1))))

Thanks, that worked great!

I would however need to upgrade the formula by also taking into account those employees currently employed, (whose End date cell= blank, i.e., FTE=1).

Thank you in advance for your precious contribution. Your help is very much appreciated

 

[s_1592]

Try placing the following formula in cell C2 ad drag-copying it to the right as needed:

=MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2)+1)/DAY(EOMONTH(C1,0))

Thanks, I tried that as well but didn't get the desired output (see screenshot below).

 

[Tetra201]

Thanks, I tried that as well but didn't get the desired output...

Try using the 1st day of the moth for the month headers instead of the last one.
The offered formula will also return 1 for those currently employed (end date cell is blank).

 

[Fluff]

@s_1592 is this for Excel or Google Sheets?

 

[s_1592]

Try using the 1st day of the moth for the month headers instead of the last one.
The offered formula will also return 1 for those currently employed (end date cell is blank).

That worked great! Thanks!