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
0
Utf
2/17/2010 3:51:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1183 Views

Similar Articles

[PageSpeed] 15

On Tue, 16 Feb 2010 19:51:01 -0800, Colin <Colin@discussions.microsoft.com>
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,EndDate2),
NETWORKDAYS(StartDate1,EndDate1)+NETWORKDAYS(StartDate2,EndDate2))

--ron
0
Ron
2/17/2010 4:13:46 AM
=IF(B1<A2,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
0
Utf
2/17/2010 4:58:01 AM
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
0
Utf
2/17/2010 5:10:01 AM
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
0
Utf
2/17/2010 7:14:05 PM
Reply:

Similar Artilces:

count the number of new lows within a range of cells
Hello, Is there a formula or method to count the number of new lows within a range of cells? For example, the following are in col A rows 5 to 14. Within this set a new low has been reached 3 times- (96,95,94). What I would like is a formula or macro that that would produce the results - 3. There are about 9000 rows of data and I am looking for "count of new lows within the preceding 10 rows" on a rolling basis. Thanks. 100 101 96 97 98 97 95 97 94 95 Version 1: Use this if the 9000 or so rows don't have blanks in them. In this version you just need to select the first cel...

Add the contents of two sheets together
I am looking for a way to add the content of two sheets or even 1 shee together. I have a large list of part numbers with quantities. Som of the part numbers are repeated. I would like to add the duplicat part number quantities together and have just 1 line with the par number and the total quantity. Does anyone have any insight? Thank -- Message posted from http://www.ExcelForum.com Hi Sounds like a job for a pivot table. See here for an intro: http://www.cpearson.com/excel/pivots.htm -- Andy. "mwiggins367 >" <<mwiggins367.16x77f@excelforum-nospam.com> wrote...

Synchronising two copies of Outlook 2003
I don't think it has ever been possible to synchronise Outlook on two PCs before. Is there any improvement to this in Office 2003? I'm not using Exchange or anything, just using a PC and a laptop at different times to access the same POP3 account. >-----Original Message----- >I don't think it has ever been possible to synchronise >Outlook on two PCs before. Is there any improvement to >this in Office 2003? I'm not using Exchange or anything, >just using a PC and a laptop at different times to access >the same POP3 account. >. >If you are using...

count string in cell
Hi All In my excel one of column is Status in detail worksheet This column have multi status. e.g. Color Error,Size Error,Data Matched. I want check how many error by type. In other sheet call Statistics, check how many status in detail worksheet. e.g. Color Error Size Error Data Matched I am using =COUNTIF('Item List'!T:T,A4) where T is Status Column. Funciton Countif just handle one value in status. Do you know which function can handle multi status ? If you want one cell to count all 3 of those criteria... One way... =SUMPRODUCT(--(ISNUMBER(MATCH(&...

Merge cells in two columns
Hi I have a worksheet with 300 rows Is it possible to merge 2 adjacent columns in one or two steps without having to do each one individually I've just learnt how to merge 2 adjacent cells to include all data in the range in the merged cell column C has the word 'dist' in ever row column D has numbers from 1 to 14 in random order using Excel 2000 Martin ���� >>Is it possible to merge 2 adjacent columns in one or two steps without having to do each one individually Easy, but you have to use a menu choice not shown on the menus by default. Do a View, Toolbars, Custom...

Count Records
Hi Guys, How can I count records in a a combobox to allow me to scroll using the slider bar without first having to scroll to the bottom of the list? Regards John Count the records in the rowsource of the combobox: NumRecord = DCount("*","NameOfTheRowSource") PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com "John" <johnlee2509@btopenworld.com> wrote in message news:0E1DBE7E-AD54-4715-A547-EC2DDE9CDE39@microsoft.com... > Hi Guys, > How can I count records in a a combobox...

how to calculate seconds elapsed between two moments?
I need the number of seconds between two moments. A1: 14:10:00 A2: 14:15:00 Difference is 5 minutes = 300 seconds. What formula(/cell format) do I need to use to get the number 300 in the result cell? When I use the formula A2-A1 I get a 00:05:00 (in time format) 0.003472222 (in General format) When I use Second(a2-a1) I get 0 how to get 300 ?? Wim Custom format the cell containing the formula to: [ss] The square brackets prevent the seconds from rolling over to minutes and / or hours when they're greater then 60. -- HTH, RD =====================================================...

Unread count is always (1) when there are no unread messages
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop I have tried everything - marking as unread, selecting all and marking as unread followed by marking all as read, cmd-alt-t, cmd-l. It still comes up with one unread message which I cannot find. <br><br>How can I fix this? On 1/22/10 12:30 AM, in article 59bb16b0.-1@webcrossing.JaKIaxP2ac0, "Lou_C@officeformac.com" <Lou_C@officeformac.com> wrote: > have tried everything - marking as unread, selecting all and marking as > unread followed by marking all as r...

Creating Groups
Hello, Using Outlook2002, is there a way to create groups so that I can select the group name for a mailing rather than the individual members? I can't figure it out and it seems that years ago you could do that. Thanks for any help and God Bless, Mark A. Sam File > New > Distribution List -- Russ Valentine [MVP-Outlook] "Mark A. Sam" <msam@Plan-It-Earth.Net> wrote in message news:%23Y%23XQ8K5FHA.2092@TK2MSFTNGP12.phx.gbl... > Hello, > > Using Outlook2002, is there a way to create groups so that I can select > the > group name for a mailing ra...

a simple count question ... ?
I'm using Excel 2007 and in column A I have a list of items that I wish to have a total count of. I have spaces (empty cells) between some items in column A to make it easier to group (and to see) but was wondering if there's a way to count the cells that only contain info. In case I haven't described this clearly, I don't want an addition of these items (they're words and not numbers), I just want to know how many cells in column A contain data. Thanks, Dave Horne Hi Dave =COUNTA(A1:A100) Regards, Per "Dave Horne" <davehorne@home.nl> skrev i m...

Outlook Meeting alerts - all day appointments
Our office sends all day appointments (so that they appear at the top of the calendar day) for work from home, vacation, and sick notices to our respective teams so that at-a-glance we can see who is out for the day. We ask that everyone select Alert = None. Recently we have experienced this issue: Alerts are being sent to the invitees. When the sender is told of this and they open their appointment, None is showing. (This never happened in the past. We're not sure what might have changed except that we all went to 2007 last August.) Is this a bug or is there some configurat...

two linked files maintained by Sharepoint
Currently, I have one excel file which has both data entry and charting in it, Roughly about 3Mb file. I would to split this document into two pieces: the data entry portion and the charting portion, where "chart.xls" would reference the cells in tdataEntry.xls" The data entry portion is too complex, to store just as a complex list in Sharepoint. But we don't need to have each use check in/check out the entire 3 MB each time. 1. It is possible to have two linked Excel documents in Sharepoint? Thus, a user could to check out only the "dataEntry.xls" or both...

Find similar numbers from two columns of numbers
I need to find duplicated numbers from two columns of numbers each about 5000 numbers . -- DennisR You may want to read some of Chip Pearson's techniques for dealing with duplicates: http://www.cpearson.com/excel/duplicat.htm Dennis Andrews wrote: > > I need to find duplicated numbers from two columns of numbers each about > 5000 numbers . > -- > DennisR -- Dave Peterson ...

Rank Query With Groups (Array?)
Hello, I have a table of data with grades which are grouped by study. I need to rank the grades relative to all the other grades in the same study. Below is an example the study and grade fields with the desired outcome for the rank. Study Grade Rank 11111 89 1 11111 75 2 11111 65 3 22222 99 1 22222 87 2 Hopefully this can be done with simple SQL in a query and not with code but I am open to anything that works. Many thanks in advance for your time and expertise! Try this -- SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE You...

Pivot table grouping problem #2
I have a pivot table that will not group numbers in a column. ie: Year Group 2004 2005 10 2 10 1 11 5 11 3 12 8 12 10 Why won't these rows combine and look like: Year Group 2004 2005 10 1 2 11 3 5 12 10 8 All other columns group fine when I try them, but the group column does not. Any ideas? Sheryl ...

count passed or failed
im having a problem getting the correct formula for this one. i used =IF((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000),"Passed","Failed") and =COUNTIF(DATA!$L$2:$L$5000, "name@email.com") and it gave me the desired output. but the problem is, i have to get the total number of passed and failed for each person in my team. i can't seem to find the exact formula to combine those conditions. please help me, thanks! Neri, =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)= (DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) *(DATA!$L$2:$L$...

Mail-enabled contacts vs Administrative groups
Hi, Since my Exchange 2003 was switched to native mode I'm not able to select a administrative group where I want to create it. Is that normal? "Jocelyn" <thefreefly@gmail.com> wrote: >Since my Exchange 2003 was switched to native mode I'm not able to >select a administrative group where I want to create it. Is that >normal? Since those are just AD objects I'm not sure I understand why you care. When Exchange had its own directory the "site" owned a directory object. But Exchange doesn't have its own directory anymore. -- Rich Matheise...

Grouping daily transactions by month (using pivot tables), across years
Hello - Could someone please let me know the best way to use a pivot table to group daily transactions into monthly totals. I noticed there is a "Group and Show Detail" option----but it seems to be unable to portray 2 separate Januarys when they fall into two separate years. For instance, if my data is 1/1/07 1000 1/4/07 8000 1/19/08 10000 I just get a total for January, rather than two separate totals for Jan 07 and Jan 08. Is there a way to allow the pivot tables to show Monthly totals, but ensure that I can distinguish between years? Is there possibly a way to in...

PDK in two companies
We are setting up a second company within PDK. Can I be an administrator/delegate for other time sheets if I am not an employee of this company? We are on GP 10 Can anyone please reply with an answer to this question? My payroll person posted this and we need to get this answered ASAP. "ASMAIL" wrote: > We are setting up a second company within PDK. Can I be an > administrator/delegate for other time sheets if I am not an employee of this > company? > > We are on GP 10 Still no replies here? Anyone? "DavidM" wrote: > Can any...

Throwing two dice and print out the total of each throw
in the days of line numbers i build a short programme to simulate the throwing of two dice with a screen print out the number of times, a particular number randomly came up . My question is can this run using Access VB ? Any constructive advise would be most welcome . The old code is as follows: REM THROWING 2 DICE CLS INPUT "How many Throws", N Dim A(12) FOR M = 1 TO 12 A(M) = 0 Next M FOR T = 1 TO N X = int(6 * rnd (1)) + 1 Y = int(6 * rnd (1)) + 1 Z = X + Y A (Z) = A(Z)+1 NEXT T CLS PRINT TAB(5); "Total" ; TAB(25); " Frequency" PRINT...

sunday is my week ending day
Hi All, When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect should be 0 because I consider Sunday my week ending day. The answer I get is 1 which is incorrect in my situation. How would I adjust for this? Thanks, rodchar You can use SET DATEFIRST to change first day of the week and check it's current value using SELECT @@DATEFIRST. But I think algorithm if DATEDIFF does not take it into account. Instead you can count number of days and divide it by 7. "rodchar" <rodchar@discussions.microsoft.com> wrote in message ...

Stats microsoft.public.windowsxp.general (last 7 days) 07-05-10
"Caveat: Quantity is not necessarily a measure of Quality" Newsgroup.................: microsoft.public.windowsxp.general Stats Were Taken..........: Mon, 05 Jul 2010 08:15:20 GMT Stats Begin...............: Mon, 28 Jun 2010 08:26:44 GMT Stats End.................: Mon, 05 Jul 2010 07:03:30 GMT Days......................: 7 Total No. of Articles.....: 606 Total No. of Characters...: 1707259 Total Volume..............: 1667 Messages Per Day..........: 86.6 Characters Per Day........: 243894.1 Average Daily Volume......: 238 kB Total Posters This Week...:...

Preventing duplicates within two columns
Hi I have been asked by a work colleague to design a spreadsheet for the issue of production logbooks. We use excel 2002 at work and the site policy is not to use VB within excel. The logbooks are issued by number and the initial version is set to version 1. For example a logbook will be issued as LB1234 Version 1. on completion of Version 1, LB1234 Version 2 is issued. So, in my spreadsheet Column A Header is Logbook Number and Column B is Version Number, there are another 15 or so columns of information with these columns. My colleague wants to be able to enter the next available Ver...

Grouping dates on a chart
I have a workbook with 12 sheets in it, one for each month of the year. Each sheet has the day of the week in column B, several other values in columns C -- J and a transaction value in column K. What I want to do is create a chart that groups the dates into days of the week and then displays a bar showing the sum of all transactions on each of those days in the month. So, for example, the sheet for March would show: 1 x x x x x x x x 1250.00 2 x x x x x x x x 80.00 3 x x x x x x x x 3000.00 4 x x x x x x x...

Grouping reports and subreports
I am trying to generate a report that is built on four separate reports based on two tables. I would like to run the report so it groups everything into one report based on Class Names. Report 1: class name, professor, exam time, room Report 2: same as above plus four other unique fields Report 3: same as above plus 2 other unique fields Report 4: same as above plus 6 other unique fields As of now, when I add them as subreports, the data is correct but instead of grouping by class name it is multiplying the results infinitely. Any insight on what I may have done wrong would be gr...