Re: Identify month in a period and run a formula

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Quantcast