=NETWORKDAYS(start_date,end_date,holidays)
In Excel 2000/2002/2003, this function requires the Analysis ToolPak to be installed. The start_date and end_date must be dates stored as serial numbers or defined using the DATE function. The holidays parameter is optional and is a reference to range of dates to be excluded besides weekends.Example:
A1=1/1/2009 (Start Date) A2=1/8/2009 (End Date) A3=1/1/2009 (Holiday) A4=1/2/2009 (Non-Working Day) A5=NETWORKDAYS(A1,A2) (= 6 Working Days) A6=NETWORKDAYS(A1,A2,A3:A4) (= 4 Working Days)
The WORKDAY() function is almost the reverse of the NETWORKDAYS function, except that the WORKDAY function finds the date that is the number of days before or after the start_date, but not including the start date.
=WORKDAY(start_date,days,holidays)
Example:
=WORKDAY(A1,6) (= 1/9/2009)
2 comments:
This only works if the user has the analysis toolpak installed and not all users do. If they don't, the spreadsheet will come up with errors.
A generic formula that would work even without the analysis toolpak would be:
=SUMPRODUCT(-(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date-1)),2)<6),-(COUNTIF(holidays , ROW(INDIRECT(start_date &":"& end_date -1)))=0))
The NETWORKDAYS function is brilliant. I am often asked about calculating the number of working days between two dates.
Post a Comment