number of days in a period that fall within another period

Question

I have 2 independent but contiguous date ranges. The first range is the start and end date for a project. Lets say start = 3/21/10 and end = 5/16/10. The second range is a month boundary (say 3/1/10 to 3/31/10, 4/1/10 to 4/30/10, etc.) I need to figure out how many days in each month fall into the first range.

The answer to my example above is March = 10, April = 30, May = 16.

I am trying to figure out an excel formula or VBA function that will give me this value.

Any thoughts on an algorithm for this? I feel it should be rather easy but I can't seem to figure it out.

I have a formula which will return TRUE/FALSE if ANY part of the month range is within the project start/end but not the number of days. That function is below.

return month_start <= project_end And month_end >= project_start

Answer 1

Think it figured it out.

=MAX( MIN(project_end, month_end) - MAX(project_start,month_start) + 1  , 0 )