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!