hours/month excluding weekends (10hr)

[cwren]

I need to calculate weekday hours in particular month; excludin
holidays and weekends, based on 10 hr work days

 

[ryanb.]

use:

Start date of month in A1
End Date of month in A2
# of Holidays (not including weekends) in cell A3

cell A4: =networkdays(A1,A2,A3)*10

HTH,

ryanb.

 

[cwren]

Is there a way to calcuate by using month and year only infffformaiton
and not first and last day of month

 

[ryanb.]

here is one way you can do it with only the month and year:

Cell A1: 11
Cell A2: 2003
Cells B1:B5 are a Named Range "Holidays" the following in each
B1: 1/1/03
B2: 5/29/03
B3: 7/4/03
B4: 11/27/03
B5: 12/25/03
(add more dates as you wish, just be sure to includes them in your named
range "Holidays")

Cell A3:
=NETWORKDAYS(VALUE($A$1&"/1/"&$A$2),IF(A1=12,VALUE($A$1-11&"/1/"&$A$2+1)-1,V
ALUE($A$1+1&"/1/"&$A$2)-1),holidays)*10

ryanb.

 

[Arvi Laanemets]

Hi

Another way

Have all holydays at least for date interval you have to deal with in table
(a single-column one will do, but you can have holiday names in another
column to better overview) on sheet Holydays (p.e. Holydays!$A$2:$A$100 - it
will cover holidays nearly for a decade)
A2=year
B2=month
MonthsWorkingHours: =
NETWORKDAYS(DATE(A2,B2,1),DATE(A2,B2+1,0),Holydays!$A$2:$A$100)*10
(it's a regular one)
When 1st and last of month are excluded by default, whatever days they are
MonthsWorkingHours: =
NETWORKDAYS(DATE(A2,B2,2),DATE(A2,B2+1,-1),Holydays!$A$2:$A$100)*10
With holidays table defined as named range
Holydays=OFFSET(Holydays!$A$2,,,COUNTIF(Holydays!$A:$A,"<>")-1,1)
yo can modify the last formula to
MonthsWorkingHours: =
NETWORKDAYS(DATE(A2,B2,2),DATE(A2,B2+1,-1),Holydays)*10

 

[ryanb.]

Definitely use Arvi's method. I was not aware a 0 in the Date function
would give you the day before the first day of the month.. very nice!!

ryanb.

Hi

Another way

Have all holydays at least for date interval you have to deal with in table
(a single-column one will do, but you can have holiday names in another
column to better overview) on sheet Holydays (p.e. Holydays!$A$2:$A$100 - it
will cover holidays nearly for a decade)
A2=year
B2=month
MonthsWorkingHours: =
NETWORKDAYS(DATE(A2,B2,1),DATE(A2,B2+1,0),Holydays!$A$2:$A$100)*10
(it's a regular one)
When 1st and last of month are excluded by default, whatever days they are
MonthsWorkingHours: =
NETWORKDAYS(DATE(A2,B2,2),DATE(A2,B2+1,-1),Holydays!$A$2:$A$100)*10
With holidays table defined as named range
Holydays=OFFSET(Holydays!$A$2,,,COUNTIF(Holydays!$A:$A,"<>")-1,1)
yo can modify the last formula to
MonthsWorkingHours: =
NETWORKDAYS(DATE(A2,B2,2),DATE(A2,B2+1,-1),Holydays)*10