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?

--
HTH

Bob Phillips

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

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)

--
HTH

Bob Phillips

"M" wrote in message
...
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:H

23)),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.