Calculate Full-time Equivalents (FTEs) in a Month Using Dates

[MntnrMark]

I'm trying to calculate full-time equivalents (FTEs) in a month. (I've used conditional formatting to shade the bars in light blue in the above example, but I added dark blue shapes for better visual representation in communicating the Start and Finish dates for this example.)

Using the example above, I know that for Row 3, in Columns F and G the FTE (which I want to show in Cells F3 and G3 as values of 1.0) should be 1.0 because one person will be required throughout the entirety of the months of Aug and Sep for Task 1. For Cells E3,D4 and F5 there will be a fractional FTE (a number less than 1.0) that I want to show in those cells, as Tasks 1, 2 and 3 all start at some point in those months. Similarly, I also know that for Cells H3, F4 and H5 there will be fractional (<1.0) FTEs that I want to show in those cells, as Tasks 1, 2 and 3 end in those months. Where there is no activity in a month (such as in Cells D3,G4, H4, D5 and E5) I want nothing to appear.

I wrote a very long formula with IFs and ANDs trying to cover every scenario, but it is very cumbersome and, because this is a very large spreadsheet with lots of values, it essentially crashed the file when I tried to copy-and-paste that long formula as many places as I needed to paste it.

Is there a simple way to calculate and show the FTEs above in Cells D3 - H5? I'm thinking I'm making this harder than it needs to be, but I'm completely stumped! Thanks, as always, for the assistance. This board is a life saver!

 

[Tetra201]

Try entering 1-Nov-2022 in E3

 

[BlueLizard]

Try entering 1-Nov-2022 in E3

Thanks for your quick reply and good spot. However this still doesn't work for me. You'll see the results in the picture, giving 2s for Person A and 1s for person B in March-23 and after.

I have removed the Holidays argument as it's not required in this instance and was creating a name error.

Thanks so much for your help with this!!

 

[Tetra201]

I cannot reproduce your results for Person A. The issue with Person B is fixed in the following formula optimized for Excel 365:

=LET(b,$B3,c,$C3,e,$E3,f,$F3,m,H$2,n,EOMONTH(m,0),(MAX(0,NETWORKDAYS(MAX(m,b),MIN(n,e+123456*(e=0)-1,c)))+MAX(0,NETWORKDAYS(MAX(m,f+123456*(f=0)+1),MIN(n,c+123456*(c=0)))))/NETWORKDAYS(m,n))

 

[BlueLizard]

I cannot reproduce your results for Person A. The issue with Person B is fixed in the following formula optimized for Excel 365:

=LET(b,$B3,c,$C3,e,$E3,f,$F3,m,H$2,n,EOMONTH(m,0),(MAX(0,NETWORKDAYS(MAX(m,b),MIN(n,e+123456*(e=0)-1,c)))+MAX(0,NETWORKDAYS(MAX(m,f+123456*(f=0)+1),MIN(n,c+123456*(c=0)))))/NETWORKDAYS(m,n))

Thanks again for your reply, I like the LET formula.

It still results in the same issue. Evaluating the formula, it is adding together the two results, rather than using one or the other. See pic below where it evaluates to (21+21)/42. I've also added the mini sheet below so it should be easier to see my result! Thank you so much for your help with this.

FTE Dates Formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	Person	Start Date	End Date		Break Start	Break End		Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23
3	Person A	01/07/2022	15/05/2023		01/11/2022	14/02/2022		2.0000	2.0000	2.0000	2.0000	1.0000	1.0000	1.0000	1.0000	1.0000	1.0000	0.4783	0.0000
4	Person B	01/10/2022	15/03/2023					0.0000	0.0000	0.0000	1.0000	1.0000	1.0000	1.0000	1.0000	0.4783	0.0000	0.0000	0.0000
Sheet1 (2)

Cell Formulas
Range	Formula
H3:S4	H3	=LET(b,$B3,c,$C3,e,$E3,f,$F3,m,H$2,n,EOMONTH(m,0),(MAX(0,NETWORKDAYS(MAX(m,b),MIN(n,e+123456*(e=0)-1,c)))+MAX(0,NETWORKDAYS(MAX(m,f+123456*(f=0)+1),MIN(n,c+123456*(c=0)))))/NETWORKDAYS(m,n))

 

[BlueLizard]

I cannot reproduce your results for Person A. The issue with Person B is fixed in the following formula optimized for Excel 365:

=LET(b,$B3,c,$C3,e,$E3,f,$F3,m,H$2,n,EOMONTH(m,0),(MAX(0,NETWORKDAYS(MAX(m,b),MIN(n,e+123456*(e=0)-1,c)))+MAX(0,NETWORKDAYS(MAX(m,f+123456*(f=0)+1),MIN(n,c+123456*(c=0)))))/NETWORKDAYS(m,n))

Just to let you know that I used a different approach, using your first formula in my original question and it appears to work.

In summary, I said IF the Break Dates are empty, use your original formula. If there they are not empty then use the original formula on the start and end dates MINUS the same formula but pointing at the break start dates instead.

Thanks very much for your help!