If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count distinct days from two groups of days
Hello,
Can anybody help please? I have used Networkdays() to count the working days between two dates. And have had to do this twice. So now have two counts that I will add together. But if the same particular date is in both count sets I dont want to count it twice? Hope this makes sense, Thanks in advance |
#2
|
|||
|
|||
Count distinct days from two groups of days
On Tue, 16 Feb 2010 19:51:01 -0800, Colin
wrote: Hello, Can anybody help please? I have used Networkdays() to count the working days between two dates. And have had to do this twice. So now have two counts that I will add together. But if the same particular date is in both count sets I dont want to count it twice? Hope this makes sense, Thanks in advance It depends on how your data is set up but If you are using NETWORKDAYS on two sets of data and there are particular dates in both sets, then it must be the case that the date ranges overlap. If the date ranges overlap, and if the holiday dates are the same for both ranges, then you could just use NETWORKDAYS using the earliest start date and the latest end date. And you could test to see if there is an overlap. So maybe something like: =IF(StartDate2=EndDate1,NETWORKDAYS(StartDate1,En dDate2), NETWORKDAYS(StartDate1,EndDate1)+NETWORKDAYS(Start Date2,EndDate2)) --ron |
#3
|
|||
|
|||
Count distinct days from two groups of days
=IF(B1A2,NETWORKDAYS(A2,B2),NETWORKDAYS(B1,B2))-1
In B3 where your ranges are in A1:A2 and B1:B2 Crude I know -- Russell Dawson Excel Student "Colin" wrote: Hello, Can anybody help please? I have used Networkdays() to count the working days between two dates. And have had to do this twice. So now have two counts that I will add together. But if the same particular date is in both count sets I dont want to count it twice? Hope this makes sense, Thanks in advance |
#4
|
|||
|
|||
Count distinct days from two groups of days
Just to add on to what Ron has posted...
If the date series are not in sequential order; try A1 = StartDate1 B1 = EndDate1 A2 = StartDate2 B2 = EndDate2 =IF(MEDIAN(A1,B1,A2)=A2,NETWORKDAYS(MIN(A1,A2), MAX(B1,B2)),NETWORKDAYS(A1,B1)+NETWORKDAYS(A2,B2)) -- Jacob "Colin" wrote: Hello, Can anybody help please? I have used Networkdays() to count the working days between two dates. And have had to do this twice. So now have two counts that I will add together. But if the same particular date is in both count sets I dont want to count it twice? Hope this makes sense, Thanks in advance |
#5
|
|||
|
|||
Count distinct days from two groups of days
Thank you all very much for the quick and useful responses.
The two date ranges are sequential inside their own sets, but one set could come wholly before, during, overlapping or after the second. Would the best way be to use the Median() check as advised by Jacob but check for A1 being the middle date also? "Jacob Skaria" wrote: Just to add on to what Ron has posted... If the date series are not in sequential order; try A1 = StartDate1 B1 = EndDate1 A2 = StartDate2 B2 = EndDate2 =IF(MEDIAN(A1,B1,A2)=A2,NETWORKDAYS(MIN(A1,A2), MAX(B1,B2)),NETWORKDAYS(A1,B1)+NETWORKDAYS(A2,B2)) -- Jacob "Colin" wrote: Hello, Can anybody help please? I have used Networkdays() to count the working days between two dates. And have had to do this twice. So now have two counts that I will add together. But if the same particular date is in both count sets I dont want to count it twice? Hope this makes sense, Thanks in advance |
Thread Tools | |
Display Modes | |
|
|