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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ibvalentine" wrote in message
...
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.


"ibvalentine" wrote in message
...
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?

Correction: Only 16 days for April.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ibvalentine" wrote in message
...
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?

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

"Fred Smith" wrote:

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" wrote in message
...
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?

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)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ibvalentine" wrote in message
...
Correction: Only 16 days for April.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ibvalentine" wrote in message
...
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?

Glad I could help. Thanks for the feedback.

Regards,
Fred.

"ibvalentine" wrote in message
...
Thanks Fred!!!!! It works great!!!

"Fred Smith" wrote:

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" wrote in message
...
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?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Fred Smith" wrote in message
...
Glad I could help. Thanks for the feedback.

Regards,
Fred.

"ibvalentine" wrote in message
...
Thanks Fred!!!!! It works great!!!

"Fred Smith" wrote:

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" wrote in message
...
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?

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.

"Don Guillett" wrote:

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