Minus Dates

  • Thread starter Thread starter K
  • Start date Start date
K

K

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.
 
Did yo look at the workday function? Workday function will exclude datys
(holidays) and it excludes weekends. If you want weekends then you can add
the weekend back in with another function.
 
try

1.
=C2-B2-SUMPRODUCT(($A$2:$A$20>B2)*($A$2:$A$20<C2))
insert it with ENTER

or


2.
=C2-B2-SUM(IF(($A$2:$A$20>B2)*($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$20>B2)*($A$2:$A$20<C2);1;))}
 
try

1.
=C2-B2-SUMPRODUCT(($A$2:$A$20>B2)*($A$2:$A$20<C2))
insert it with ENTER

or

2.
=C2-B2-SUM(IF(($A$2:$A$20>B2)*($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$20>B2)*($A$2:$A$20<C2);1;))}



- Show quoted text -
Click to expand...

Thats brilliant. Thanks Jarek
 
Joel,
I was interested in your comment re 'adding the weekends back in with
another function' as this sounds exactly what I am looking for.
Using the WORKDAY function I can ignore holidays but I need to add back in
the weekends which need (in my case) to be included in the calc.
Thanks,
 
Back
Top