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]

If you calculate fractional FTEs using calendar days, try the following:

=MAX(0,MIN($C3,EOMONTH(D$1,0))-MAX($B3,D$1)+1)/DAY(EOMONTH(D$1,0))

If you calculate fractional FTEs using business days, try the following:

=MAX(0,NETWORKDAYS(MAX($B3,D$1),MIN($C3,EOMONTH(D$1,0)),Holidays))/NETWORKDAYS(D$1,EOMONTH(D$1,0),Holidays)

where Holidays is a named range containing holiday dates.
Finally, if you do not want see 0.0 FTEs, custom-format the cells as 0.0;;

 

[MntnrMark]

If you calculate fractional FTEs using calendar days, try the following:

=MAX(0,MIN($C3,EOMONTH(D$1,0))-MAX($B3,D$1)+1)/DAY(EOMONTH(D$1,0))

If you calculate fractional FTEs using business days, try the following:

=MAX(0,NETWORKDAYS(MAX($B3,D$1),MIN($C3,EOMONTH(D$1,0)),Holidays))/NETWORKDAYS(D$1,EOMONTH(D$1,0),Holidays)

where Holidays is a named range containing holiday dates.
Finally, if you do not want see 0.0 FTEs, custom-format the cells as 0.0;;

FANTASTIC! However, there is only one minor (I hope adjustment) to make. When a task extends into only the first day of a month (i.e., the last day of the task is on the first day of a month) the formula is going me a zero; it needs to give 1/31, or 1/30 (Apr, Jun, Sep, Nov), or 1/28 (Feb). Is there a way to modify the formula to do that? Otherwise, it does EXACTLY what I needed. Thanks so much!

 

[Tetra201]

... When a task extends into only the first day of a month ... the formula is going me a zero; it needs to give 1/31...

When formatted as 0.0;; 1/31 does appear as 0.0. Try formatting the cell as 0.00;; and you should see 0.03.

 

[MntnrMark]

Thank you very much, Tetra201!

 

[BlueLizard]

If you calculate fractional FTEs using calendar days, try the following:

=MAX(0,MIN($C3,EOMONTH(D$1,0))-MAX($B3,D$1)+1)/DAY(EOMONTH(D$1,0))

If you calculate fractional FTEs using business days, try the following:

=MAX(0,NETWORKDAYS(MAX($B3,D$1),MIN($C3,EOMONTH(D$1,0)),Holidays))/NETWORKDAYS(D$1,EOMONTH(D$1,0),Holidays)

where Holidays is a named range containing holiday dates.
Finally, if you do not want see 0.0 FTEs, custom-format the cells as 0.0;;

Thanks so much for this! Really helpful.

In addition to this I have another Start and End Date column where between these dates I'd like the FTE to be reduced to zero/pro-rated down. End result hopefully as follows:

Are you able to assist with this? Many thanks in advance!!!

 

[BlueLizard]

I can't edit my post, but I am using the networkdays solution you provided, in case that's useful to know.

 

[Tetra201]

The following formula returns the expected results for the dataset in Post #6:

=MAX(0,NETWORKDAYS(MAX($B3,G$2),MIN($E3+123456*($E3=0)-1,EOMONTH(G$2,0)),Holidays))/NETWORKDAYS(G$2,EOMONTH(G$2,0),Holidays)+MAX(0,NETWORKDAYS(MAX($F3+123456*($F3=0)+1,G$2),MIN($C3+123456*($C3=0),EOMONTH(G$2,0)),Holidays))/NETWORKDAYS(G$2,EOMONTH(G$2,0),Holidays)

 

[BlueLizard]

The following formula returns the expected results for the dataset in Post #6:

=MAX(0,NETWORKDAYS(MAX($B3,G$2),MIN($E3+123456*($E3=0)-1,EOMONTH(G$2,0)),Holidays))/NETWORKDAYS(G$2,EOMONTH(G$2,0),Holidays)+MAX(0,NETWORKDAYS(MAX($F3+123456*($F3=0)+1,G$2),MIN($C3+123456*($C3=0),EOMONTH(G$2,0)),Holidays))/NETWORKDAYS(G$2,EOMONTH(G$2,0),Holidays)

Remarkable. Thank you very much! Now to try and understand what's going on.

 

[BlueLizard]

The following formula returns the expected results for the dataset in Post #6:

=MAX(0,NETWORKDAYS(MAX($B3,G$2),MIN($E3+123456*($E3=0)-1,EOMONTH(G$2,0)),Holidays))/NETWORKDAYS(G$2,EOMONTH(G$2,0),Holidays)+MAX(0,NETWORKDAYS(MAX($F3+123456*($F3=0)+1,G$2),MIN($C3+123456*($C3=0),EOMONTH(G$2,0)),Holidays))/NETWORKDAYS(G$2,EOMONTH(G$2,0),Holidays)

Hi Tetra, I've just got round to implementing this and it doesn't appear to work for me.

Your solution references G$2 when I think you meant H$2 - so I changed that but I get the following results still (results above 1.0) Any ideas?

Many, many thanks in advance!