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

[jereece]

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

 

[micahs_10]

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]

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]

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]

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]

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]

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]

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!