NETWORKDAYS....for 10 hours or 12 hour workers

jereece

New Member
Joined
Mar 18, 2002
Messages
25
I have a spreadsheet that calculates the total number of days our vendors work. Column A is Start Date, Column B is Release Date, Column C is the NETWORKDAYS formula. Problem is our vendors work 10 hour days (i.e. 4 days a week). How can I use NETWORKDAYS to calculate for these people?

Also, I noticed if Column A is 1/1/2005 and Column B is 1/3/2005, NETWORKDAYS shows only 1 day worked "=NETWORKDAYS(A1,B1)". Why? It should be 3 days.


Thanks,
Jim
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

micahs_10

Board Regular
Joined
Sep 2, 2004
Messages
83
Well, to answer the 2nd part of your question. Networkdays between 1/1/2005 and 1/3/2005 is only 1 day because 1/1 and 1/2 are the weekend, which are not counted using that function.

Not sure about your 10 hour day problem though.

...edit, just thought of this. You could add another column, column D, that will have each Friday listed, ie. 1/7/2005. Then edit your formula to look like this. =NETWORKDAYS(A1,B1,D1) This will recognize each Friday as a holiday and, therefore, not worked. So the formula will return 4 days a week, not 5. Hope you can use that.
Micah
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
By design, NETWORKDAYS function does not include weekend days, commonly Saturday and Sunday. Also, if a list of holidays is provided, it will also exclude those dates.

In the case of your example, 1/1 and 1/2 are weekend days and are therefore excluded from the result.

I am sure the Excel experts can find an easier way to do this, but my way to resolve this would be =(DAYS360(A1,B1))+1 to get the total count of days inclusive of start and end dates.
 

jereece

New Member
Joined
Mar 18, 2002
Messages
25
Okay my stupidity on 1/1 and 1/2 being weekend days.

On the way to calculate NETWORKDAYS for 4-10 workers, I am not sure that =NETWORKDAYS(A1,B1,D1) will work because I have many weeks between my start and end dates. For example, some start dates are 1/1/05 and the end date is 12/31/05. So there would be many fridays. Are there any other suggestions?

I really appreciate the help.

Jim
 

micahs_10

Board Regular
Joined
Sep 2, 2004
Messages
83

ADVERTISEMENT

Maybe an option would be to list all Fridays (you can just drag it down) in column D for whatever time period your using, lets say all of 2005, so there'd be 52 Fridays. Then edit your formula to look like:
=NETWORKDAYS(A1,B1,$D$1:$D$52)

This will not count Fridays as workdays for whatever tiem frame you're using...as long as it's 2005. Just expand the list of Friday's (D1:D52) for other years, etc.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,825
Office Version
  1. 365
Platform
  1. Windows
An alternative approach for a 4 day week, Monday through Thursday...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<5),--(1-ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C5,0))))

...where A1 contains the start date, B1 the end date, and C1:C5 your list of holidays.

Hope this helps!
 

jereece

New Member
Joined
Mar 18, 2002
Messages
25
Works great, however I entered 1/1/2005 in cell C1 (holiday) and it did not change the calculation (D1) when A1 (Start Date) is 1/1/2005 and B1 (End Date) is 1/8/2005. I proably don't need the holiday list however.

Thanks,
Jim
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,825
Office Version
  1. 365
Platform
  1. Windows
That's because 1/1/2005 falls on a Saturday and is already taken into consideration by the formula. So with 1/1/2005 as a holiday, there are in fact 4 working days from 1/1/2005 to 1/8/2005 in a Monday through Thursday work week.

Hope this helps!
 

Forum statistics

Threads
1,147,510
Messages
5,741,582
Members
423,669
Latest member
necat02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top