NETWORKDAYS FUNCTION, Help please

[BiggyTwo]

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 = "masonry" and Range2="John Smith"?

Thanks for your help.

BC

 

[Bob Phillips]

You 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)

 

[BiggyTwo]

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 are:-
1) The employee name, e.g. "John Smith" and
2) The discipline he worked in, e.g. "masonry"

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

Best wishes,

bc

 

[SteveG]

Bob is right, more info would be helpful but I made an assumption. M
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 th
networkdays is greater than 10 where John Smith was the contracto
performing masonry work.

=SUMPRODUCT((C2:C7>10)*(D27="masonry")*(E2:E7="John Smith"))

HTH

Stev

 

[SteveG]

Based on your last post, if you use a table set up as I said then,

=SUMPRODUCT((D27="masonry")*(E2:E7="John Smith")*C2:C7)

HTH

Steve

 

[Bob Phillips]

=NETWORKDAYS(INDEX(C1:C15,MATCH(1,(A1:A15="James
Smith")*(B1:B15="masonry"),0)),INDEX(D115,MATCH(1,(A1:A15="James
Smith")*(B1:B15="masonry"),0)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[BiggyTwo]

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.

b

 

[Bob Phillips]

Did you see my follow-up response?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ron Rosenfeld]

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.

b

=SUMPRODUCT((WEEKDAY(Dates,3)<5)*ISNA(MATCH(
Dates,Holidays,0))*(Names="George")*(Discipline="Plumbing"))

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

 

[BiggyTwo]

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