Number of overlapping working days with table date ranges

[trishcollins]

I have a table "Resource_Assignments" that list resources and projects they are working on. Resources can have multiple project assignments (one assignment per row), and there is a potential for overlapping dates. Each project has a start date and end date. I am able to calculate the number of projects where the resource has overlapping days, but I need to calculate the following:

Total number of overlapping "working" days for each resource for a particular project assignment
Number of remaining overlapping "working" days for each resource based on today's date "today()" for a particular project assignment

Column names are "Resource" "Start Date" "Estimated End Date" "Today's Date"

I have searched and have not been able to find a similar formula anywhere. I can do this in VBA, so if but prefer to do it in a formula if possible.

Can anyone help?

Trish

 

[AFPathfinder]

Mike Girvin could probably figure out a set of cell functions to accomplish the task, but I'm not seeing any non-VBA solutions. You could have all rows calculate off a single static date, but to have each row calculate off a range of dates is stumping me as well. The closest solution I can see is building a UDF for that purpose.

 

[trishcollins]

Thanks. I was avoiding VBA, just trying to keep it simple, but if anyone wants to write some code for me, I would be happy to implement it