Minus Dates
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. |
Minus Dates
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. "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. |
Minus Dates
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. |
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 |
Minus Dates
welcome
pls click YES if it helped you On 23 Mar, 12:24, K wrote: 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- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
Minus Dates
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, -- Traa Dy Liooar Jock "joel" wrote: 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. "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. |
All times are GMT +1. The time now is 03:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com