Number of days in a specific month between various start and end d

[ibvalentine]

I am looking for a formula that will return the number of days of a
particular month between start and end dates.

For example, if the start and end dates are as follows:

2/23/09 5/6/09
3/12/09 3/28/09
3/2/09 4/16/09

How many days is there in April for each of the three start and end dates?
For the first record, there should be 30 days, for the second, 16 days, and
for the third, 55 days, but what is the formula?

 

[Don Guillett]

?? How can April have 55 days. I always thought it was limited to 30 days.

 

[Fred Smith]

Assuming the '55 days' in April is a typo, you probably want something like
this:

=MAX(0,MIN(B1,DATE(2009,5,1))-MAX(A1,DATE(2009,4,1)))

It will return zero if the date range doesn't span any days in April, as in
your second example.

Regards,
Fred.

 

[ibvalentine]

Correction: Only 16 days for April.

?? How can April have 55 days. I always thought it was limited to 30 days.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[ibvalentine]

Thanks Fred!!!!! It works great!!!

Assuming the '55 days' in April is a typo, you probably want something like
this:

=MAX(0,MIN(B1,DATE(2009,5,1))-MAX(A1,DATE(2009,4,1)))

It will return zero if the date range doesn't span any days in April, as in
your second example.

Regards,
Fred.

 

[Don Guillett]

Where i1 is the start date and j1 is the stop date and o1 is a date of the
1st of the month desired. 4/1/2009
Works for all. If you change o1 to 3/1. copy down to get 30,0,16.

=SUMPRODUCT((ROW(INDIRECT(I1&":"&J1))>=$O$1)*(ROW(INDIRECT(I1&":"&J1))<DATE(YEAR($O$1),MONTH($O$1)+1,1)))

 

[Fred Smith]

Glad I could help. Thanks for the feedback.

Regards,
Fred.

 

[Don Guillett]

Did you test with other dates such as 1 jan to 31 dec.

 

[ibvalentine]

No, I just tested your formula. Fred's formula worked for my particular case,
but does not work throughout the calendar year. Yours works in all cases.

Thanks for the input.