Re: Identify month in a period and run a formula

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" <M@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:32263D5B-955D-46F3-A838-C0A45FEFFD04@xxxxxxxxxxxxxxxx
> 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" <M@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:F5497A77-78AB-4EDF-9E35-C3D22CE5446B@xxxxxxxxxxxxxxxx
> > > 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 ***). 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.
> >
> >
> >


.