Identify month in a period and run a formula

[Guest]

I am having trouble with creating this one formula. Can anyone help? I have
two columns of dates (start and finish dates). I would like to identify a
month (e.g April)that occurs in the period between start and finish and then
if the month of April does occur in the period, calculate the number of
months between start and finish and divide by a constant (a figure saved on a
different sheet). So far I have this:
=IF(AND(("01/04/2005">=MIN(I20:I23,H20:H23)),"31/04/2005"<=MAX(I20:I23,H20:H23)),SUM((D18/((YEAR(I20:I23)-YEAR(H20:H23))*12+MONTH(I20:I23)-MONTH(H20:H23)))/60),"0")
but I need one formula that does this if it meets the condition and then sum
the results. At the moment it returns zero if all the dates do not meet the
condition.

 

[Bob Phillips]

I am not too sure but try this

=IF(AND(MONTH(MIN(I20:I23,H20:H23))=4,YEAR(MIN(I20:I23,H20:H23))=2005),D18/S
UMPRODUCT(--(MONTH(H20:I23)=4),--(YEAR(H20:I23)=2005)),0)

 

[Guest]

Thanks, though I might be missing something, because it works if the month is
in either the start or finish date but is there any way it can pick up on the
month that occurs within the range of dates?
e.g
Start 01/01/04 and finish 31/03/05. If the range includes month of Jan (in
the year 2005) then calculate the number of months between start and finish
date. If each row meets this condition, working through the column, run the
rest of the formula and total the results.

 

[Bob Phillips]

I need more information. Are you asking to count all months in a range that
fall between two other dates? If so, where are these dates?

 

[Guest]

Yes, and the dates are in two columns, one column is the start date and the
second column the finish date. Each row is a unique record of a project. If
the range of dates for that row (or project) includes April for example, then
I would like to then count the number of months between the start and finish
date for that project (the count should not happen if the first part of the
formula does not return a 'true' for 'April'). The problem is I need to do
this for all the rows and then total the result in one formula.