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

  • Thread starter Thread starter ibvalentine
  • Start date Start date
I

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?
 
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.
 
Correction: Only 16 days for April.

Don Guillett said:
?? 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)
Click to expand...
 
Thanks Fred!!!!! It works great!!!

Fred Smith said:
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.
Click to expand...
 
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)))
 
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.
 
Back
Top