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

#### MntnrMark

##### New Member

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
10.2 KB · Views: 3
• 1613925010880.png
10.9 KB · Views: 3

### 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
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
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
... 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

##### New Member
Thank you very much, Tetra201!

Replies
10
Views
158
Replies
0
Views
315
Replies
1
Views
83
Replies
2
Views
77
Replies
1
Views
171

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.

### Which adblocker are you using?

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

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