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

MntnrMark

New Member
Joined
Dec 18, 2012
Messages
40
1613926227774.png


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!
 

Attachments

  • 1613924735831.png
    1613924735831.png
    10.2 KB · Views: 3
  • 1613925010880.png
    1613925010880.png
    10.9 KB · Views: 3

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
If you calculate fractional FTEs using calendar days, try the following:
Excel Formula:
=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:
Excel Formula:
=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

New Member
Joined
Dec 18, 2012
Messages
40
If you calculate fractional FTEs using calendar days, try the following:
Excel Formula:
=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:
Excel Formula:
=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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
... 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,643
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top