software

Hello All,

How can I use the networkdays function to count the number of workdays
between two given days with 2 conditions attached?

Example: How many workdays are there between date1 and date2 while
Range1 = quot;masonryquot; and Range2=quot;John Smithquot;?

Thanks for your help.

BCYou don't explain how the conditions relate to the dates. Does each item in
the ranges have start and end dates associated with them or what?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;BiggyTwoquot; gt; wrote in message oups.com...
gt; Hello All,
gt;
gt; How can I use the networkdays function to count the number of workdays
gt; between two given days with 2 conditions attached?
gt;
gt; Example: How many workdays are there between date1 and date2 while
gt; Range1 = quot;masonryquot; and Range2=quot;John Smithquot;?
gt;
gt; Thanks for your help.
gt;
gt; BC
gt;
Hi Bob,

Thanks for your time.

John Smith is one of several multiskilled employees on a project. The
aim is to count the number of normal workdays that any employee (John
Smith etc.) works as a mason, welder etc.
That is the conditions a-
1) The employee name, e.g. quot;John Smithquot; and
2) The discipline he worked in, e.g. quot;masonryquot;

(Not sure I understand your question but hope that the short
explanation helps.)

Best wishes,

bc
Bob is right, more info would be helpful but I made an assumption. My
table is set up as follows to make this formula work.

Column A = Start Date, Column B = End Date, Column C = Networkdays,
Column D = Type of work (masonry, carpentry), Column E = Contractor

Then I applied this formula which counts the number of times the
networkdays is greater than 10 where John Smith was the contractor
performing masonry work.

=SUMPRODUCT((C2:C7gt;10)*(D27=quot;masonryquot;)*(E2:E7=quot;J ohn Smithquot;))

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=527585
Based on your last post, if you use a table set up as I said then,

=SUMPRODUCT((D27=quot;masonryquot;)*(E2:E7=quot;John Smithquot;)*C2:C7)

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=527585=NETWORKDAYS(INDEX(C1:C15,MATCH(1,(A1:A15=quot;James
Smithquot;)*(B1:B15=quot;masonryquot;),0)),INDEX(D115,MATCH( 1,(A1:A15=quot;James
Smithquot;)*(B1:B15=quot;masonryquot;),0)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;BiggyTwoquot; gt; wrote in message oups.com...
gt; Hi Bob,
gt;
gt; Thanks for your time.
gt;
gt; John Smith is one of several multiskilled employees on a project. The
gt; aim is to count the number of normal workdays that any employee (John
gt; Smith etc.) works as a mason, welder etc.
gt; That is the conditions a-
gt; 1) The employee name, e.g. quot;John Smithquot; and
gt; 2) The discipline he worked in, e.g. quot;masonryquot;
gt;
gt; (Not sure I understand your question but hope that the short
gt; explanation helps.)
gt;
gt; Best wishes,
gt;
gt; bc
gt;

Hello all,
Once again thanks for your time. I shall give the problem another try,
perhaps change the spreadsheet if I am allowed to get the desired
results. There must be a way.

Just a little note:
ColumnA contains all the Dates anyone worked (Sun to Sat).
ColumnD contains the names
ColumnG contains the discipline, eg masonary.
I would like to count the weekdays, less holidays, that John performed
masonry, etc. then expand to other combinations.

Appreciate your time, sorry I wasted so much of it though.

bDid you see my follow-up response?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;BiggyTwoquot; gt; wrote in message oups.com...
gt;
gt; Hello all,
gt; Once again thanks for your time. I shall give the problem another try,
gt; perhaps change the spreadsheet if I am allowed to get the desired
gt; results. There must be a way.
gt;
gt; Just a little note:
gt; ColumnA contains all the Dates anyone worked (Sun to Sat).
gt; ColumnD contains the names
gt; ColumnG contains the discipline, eg masonary.
gt; I would like to count the weekdays, less holidays, that John performed
gt; masonry, etc. then expand to other combinations.
gt;
gt; Appreciate your time, sorry I wasted so much of it though.
gt;
gt; b
gt;
On 29 Mar 2006 09:04:54 -0800, quot;BiggyTwoquot; gt; wrote:

gt;
gt;Hello all,
gt;Once again thanks for your time. I shall give the problem another try,
gt;perhaps change the spreadsheet if I am allowed to get the desired
gt;results. There must be a way.
gt;
gt;Just a little note:
gt;ColumnA contains all the Dates anyone worked (Sun to Sat).
gt;ColumnD contains the names
gt;ColumnG contains the discipline, eg masonary.
gt;I would like to count the weekdays, less holidays, that John performed
gt;masonry, etc. then expand to other combinations.
gt;
gt;Appreciate your time, sorry I wasted so much of it though.
gt;
gt;b=SUMPRODUCT((WEEKDAY(Dates,3)lt;5)*ISNA(MATCH(
Dates,Holidays,0))*(Names=quot;Georgequot;)*(Discipline=quot;P lumbingquot;))

You'll need a range with the Holiday dates.

You can also substitute cell references for the other named ranges (Dates,
Names, Discipline) but be aware that the ranges cannot include an entire column
and these three must be the same size.

You can also put George and Plumbing in some cell to make it easier to change
the parameters.--ron

Hi All,

Thanks one more time. The posts from Ron and Bob offer the most
promise. After a bit of *distillation* I shall confirm in another 24
hours.

Regards,

b