working with dates

Hi!

> IF H5 or J5 or L5 is >F1 and <=H1 count as 1.

So, does that mean if all 3 cells meet the criteria it still counts as 1?

F1 = lower boundry date
H1 = upper boundry date

=IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5>=F1),--(H5:L5<=H1)),1,0)

Biff

"Pe66les" <[email protected]> wrote in message
news:[email protected]...
> How can I write this expression so that my answer is not #VALUE?
> I'm working with dates, and I want to count only the dates in columns H,
> J,
> & L if they fall between the dates entered in F1 and H1.
> IF H5 or J5 or L5 is >F1 and <=H1 count as 1.

On Tue, 23 Aug 2005 17:50:03 -0700, "Pe66les"
<[email protected]> wrote:

>How can I write this expression so that my answer is not #VALUE?
>I'm working with dates, and I want to count only the dates in columns H, J,
>& L if they fall between the dates entered in F1 and H1.
>IF H5 or J5 or L5 is >F1 and <=H1 count as 1.

I'm not sure if you want to count each qualifying date as 1 (so your result
range would be 0 to 3) or count 1 if any of the dates qualify (so your result
range would be 0 to 1)

For the former:

=AND(H5>F1,H5<=H1)+AND(J5>F1,J5<=H1)+AND(L5>F1,L5<=H1)

For the latter:

=--OR(AND(H5>F1,H5<=H1)+AND(J5>F1,J5<=H1)+AND(L5>F1,L5<=H1))


--ron

Yes, if any or all 3 cells meet the criteria it only counts as 1.

"Biff" wrote:

> Hi!
>
> > IF H5 or J5 or L5 is >F1 and <=H1 count as 1.
>
> So, does that mean if all 3 cells meet the criteria it still counts as 1?
>
> F1 = lower boundry date
> H1 = upper boundry date
>
> =IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5>=F1),--(H5:L5<=H1)),1,0)
>
> Biff
>
> "Pe66les" <[email protected]> wrote in message
> news:[email protected]...
> > How can I write this expression so that my answer is not #VALUE?
> > I'm working with dates, and I want to count only the dates in columns H,
> > J,
> > & L if they fall between the dates entered in F1 and H1.
> > IF H5 or J5 or L5 is >F1 and <=H1 count as 1.
>
>
>

How can I write this expression so that my answer is not #VALUE?
I'm working with dates, and I want to count only the dates in columns H, J,
& L if they fall between the dates entered in F1 and H1.
IF H5 or J5 or L5 is >F1 and <=H1 count as 1.

Hi!

> IF H5 or J5 or L5 is >F1 and <=H1 count as 1.

So, does that mean if all 3 cells meet the criteria it still counts as 1?

F1 = lower boundry date
H1 = upper boundry date

=IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5>=F1),--(H5:L5<=H1)),1,0)

Biff

"Pe66les" <[email protected]> wrote in message
news:[email protected]...
> How can I write this expression so that my answer is not #VALUE?
> I'm working with dates, and I want to count only the dates in columns H,
> J,
> & L if they fall between the dates entered in F1 and H1.
> IF H5 or J5 or L5 is >F1 and <=H1 count as 1.

On Tue, 23 Aug 2005 17:50:03 -0700, "Pe66les"
<[email protected]> wrote:

>How can I write this expression so that my answer is not #VALUE?
>I'm working with dates, and I want to count only the dates in columns H, J,
>& L if they fall between the dates entered in F1 and H1.
>IF H5 or J5 or L5 is >F1 and <=H1 count as 1.

I'm not sure if you want to count each qualifying date as 1 (so your result
range would be 0 to 3) or count 1 if any of the dates qualify (so your result
range would be 0 to 1)

For the former:

=AND(H5>F1,H5<=H1)+AND(J5>F1,J5<=H1)+AND(L5>F1,L5<=H1)

For the latter:

=--OR(AND(H5>F1,H5<=H1)+AND(J5>F1,J5<=H1)+AND(L5>F1,L5<=H1))


--ron

Yes, if any or all 3 cells meet the criteria it only counts as 1.

"Biff" wrote:

> Hi!
>
> > IF H5 or J5 or L5 is >F1 and <=H1 count as 1.
>
> So, does that mean if all 3 cells meet the criteria it still counts as 1?
>
> F1 = lower boundry date
> H1 = upper boundry date
>
> =IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5>=F1),--(H5:L5<=H1)),1,0)
>
> Biff
>
> "Pe66les" <[email protected]> wrote in message
> news:[email protected]...
> > How can I write this expression so that my answer is not #VALUE?
> > I'm working with dates, and I want to count only the dates in columns H,
> > J,
> > & L if they fall between the dates entered in F1 and H1.
> > IF H5 or J5 or L5 is >F1 and <=H1 count as 1.
>
>
>