Minus Dates
On Mar 23, 10:35*am, Jarek Kujawa wrote:
try
1.
=C2-B2-SUMPRODUCT(($A$2:$A$20B2)*($A$2:$A$20<C2))
insert it with ENTER
or
2.
=C2-B2-SUM(IF(($A$2:$A$20B2)*($A$2:$A$20<C2),1,))
CTRL+SHIFT+ENTER this formula as it is an array-formula
if you insert it correctly curly brackets {} will appear and the
formula will look like this
{=C2-B2-SUM(IF(($A$2:$A$20B2)*($A$2:$A$20<C2);1;))}
On 23 Mar, 11:09, K wrote:
I have dates in Range("A2:A20") like see below
01/04/2009
05/04/2009
06/04/2009
10/04/2009
11/05/2009
etc………
And again I got date in cell B2 and in cell C2 and I want formula in
cell D2 which should return the number of days between the dates which
I have in cell B2 and C2 excluding the days for which I have dates *in
Range("A2:A20"). *For example at the moment I got date "31/03/2009" in
cell B2 and date "10/04/2009" in cell C2 and I got formula "=C2-B2" in
cell D2 which return the result 10 days. *But I want formula somthing
like "=C2-B2-any date in range("A2:A20") equal to or more than B2- any
date in range("A2:A20") equal to or less than C2", in other words i
want to exclud the days for which i have dates in Range("A2:A20") from
the formula which i have in *D2. *I hope i was able to explain my
question. *Please can any friend have any suggestion or solution for
what i should be doing.- Hide quoted text -
- Show quoted text -
Thats brilliant. Thanks Jarek
|