hours/month excluding weekends (10hr)

  • Thread starter Thread starter cwren
  • Start date Start date
C

cwren

I need to calculate weekday hours in particular month; excludin
holidays and weekends, based on 10 hr work days
 
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.
 
Is there a way to calcuate by using month and year only infffformaiton
and not first and last day of month
 
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.
 
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
 
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.

Arvi Laanemets said:
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
Click to expand...
 
Back
Top