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.

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.

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.

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.

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.

"Bob Phillips" wrote:

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.