Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minus Dates | Excel Discussion (Misc queries) | |||
Formula for dates minus the weekends | Excel Worksheet Functions | |||
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS | Excel Discussion (Misc queries) | |||
Calculate Number of Days Between Dates Minus Weekends | Excel Discussion (Misc queries) | |||
Chronolize dates with varying days minus weekends | Excel Worksheet Functions |