There are several important functions for working with dates and times in Excel:NETWORKDAYS and WORKDAY, INT and MOD. The first two require a reference to the Analysis ToolPak. If you use Excel 2007, you already have these functions baked in, but for the rest of us, go to Tools » Add-Ins and check off Analysis ToolPak. If you don’t see Analysis ToolPak listed, it may not be installed. You’ll need to install it from your Office CD.
(Note: Descripttions of each function were taken from: Excel Function Reference)
NETWORKDAYS
If you need to count the number of workdays in between two dates, use the NETWORKDAYS function.
NETWORKDAYS(start_date,end_date,holidays)
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
Arguments:
Start_date is a date that represents the start date.
End_date is a date that represents the end date.
Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.
Example: Calculate elapsed business days
Cell A1 contains a start date, B1 an end date. “HolidaysList” is a named range with a list of federal holidays which do not count as business days (A6:A15 in the picture below). The formula to return the number of working days (excluding holidays and weekends) in between those dates is
=NETWORKDAYS(A1,B1,HolidaysList)-1
CAclubindia
